Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Calculate Total Time Run

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Calculate Total Time Run
 Login/Join
 
Gagan Marwah
Silver Member
posted
Hello all,

I have two fields I am working with here.. Batch_ID and Time_Run

Ex:

Batch_ID Time_Run
A123 04/12/2019 14:15:33.347
A123 04/12/2019 15:20:17.167
B567 04/13/2019 10:07:12.016
B567 04/13/2019 10:15:18.245
B567 04/13/2019 10:17:33.300
B567 04/13/2019 10:35:43.187
CC257 04/15/2019 04:00:33.067
CC257 04/15/2019 04:48:28.467


I would like to calculate how long did it take to run a Batch job. I know I can use HDIFF to calculate the difference between two dates but how do I get difference when working with one one date column?

For Ex: Duration for Batch_ID A123 in hours is 1, in minutes is 64, and in seconds is 3884

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8
Windows, All Outputs
 
Posts: 29 | Registered: September 05, 2017Report This Post
BabakNYC
Virtuoso
posted Hide Post
What is the duration for batch id B567? Are you just comparing the first batch id to the last one? If that's the case, you should:
1. Make sure the data is sorted by Batch ID.
2. Use LAST syntax to find the last batch id and flag it so you know what two values have to be subtracted to get the difference.

Ideally, your database would have a START and END date times so you could avoid all this because you'll also have to account for when the clock resets from 23:59 to 0:00 at midnight and if your times are split between two dates, your calculation would have to account for that.

HDIFF doc has an example specific to time in the bottom of the page https://webfocusinfocenter.inf...ver/source/htime.htm

This message has been edited. Last edited by: BabakNYC,


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Wep5622
Virtuoso
posted Hide Post
I would suggest a multi-verb for calculating the difference between a batch's earliest and last timestamps. Off the top of my head:
TABLE FILE X
SUM
    MIN.Time_Run
    MAX.Time_Run
    COMPUTE Time_Spent/I10 = HDIFF(MAX.Time_Run, MIN.Time_Run, 'SECOND', Time_Spent);
BY Batch_ID
PRINT
    COMPUTE Step_Time/I10  = HDIFF(Time_Run, LAST Time_Run, 'SECOND', Step_Time);
BY Batch_ID
BY Time_Run NOPRINT
END


You probably want to special-case the first row of Step_Time though, as you'll get a huge number otherwise (Batch start time - basedate:'1900/12/31').


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Calculate Total Time Run

Copyright © 1996-2020 Information Builders