I am looking for a means to compute the time difference between two date-time values, into a date-time variable, and report it in the natural fashion. For example:
By "natural" I mean that leading null components (and their separators) would be suppressed (space-filled) in the otput. Can it be done?
I can use HDIFF to get the elapsed time in ms, as an integer. One would expect (or at least hope) that overly large values placed in a component by HSETPT() would "carry" into the higher-order positions, so that setting the 'ms' value to 16,210,245 (in our case) would result in the Elapsed value shown above. Doesn't seem to work for me. Any suggestions?This message has been edited. Last edited by: <Kathryn Henning>,
December 01, 2014, 05:05 PM
Waz
Are these in a date time field ?
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
December 02, 2014, 03:48 AM
Wep5622
The difference between two datetimes would not result in a valid datetime value; you can't have day 0 or month 0, after all - it's semantically incorrect.
The SQl92 standard solved this by using a special interval data-type for differences between datetimes. Unfortunately, AFAIK WebFOCUS did not yet implement that standard, even if you connect to an SQL92 compliant RDBMS.
With that background, I doubt you could wrangle anything better than an alphanumeric field out of WebFOCUS for your purpose.
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 :
December 02, 2014, 06:09 AM
Tony A
Jack,
As Wep states, not unless it's an alpha-numeric output that you want.
Here's a little FUNCTION that I use which could be extended to provide year and month components also -
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
December 02, 2014, 01:35 PM
j.gross
@wep -- Thanks for the confirmation that it cannot be done in the natural way, absent a New Feature.
@tony -- I wound up doing something equivalent (with a pair of DEFINE FUNCTIONs).
-jg
- Jack Gross WF through 8.1.05
December 02, 2014, 02:52 PM
j.gross
Actually, defining years and months elapsed gets dicey, since months vary in length, so "ddd hh:mi:ss.mmm" seems just right.
December 02, 2014, 04:27 PM
Waz
Something to consider. In V7.1 you could use '00000000000000000' instead of '00010101000000000', and it worked perfectly
So this has issues at the Day level.
DEFINE FUNCTION HTIMEDIFF(ENDTIME/HYYMDs, BEGTIME/HYYMDs)
DT_NULL/HYYMDs = HINPUT(14, '00010101000000000', 8, 'HHIS');
HTIMEDIFF/HYYMDs = HADD(DT_NULL, 'MILLISECONDS',HDIFF(ENDTIME, BEGTIME, 'MILLISECONDS', 'D12.2'), 8, 'HYYMDs');
END
DEFINE FILE CAR
BEG_TIME/HYYMDs = HINPUT(23, '2014-12-01 09:12:34.100', 8, 'HYYMDs');
END_TIME/HYYMDs = HINPUT(23, '2014-12-01 13:42:44.345', 8, 'HYYMDs');
END
TABLE FILE CAR
SUM BEG_TIME
END_TIME
COMPUTE C_DIFF/HYYMDs = HTIMEDIFF(END_TIME, BEG_TIME);
BY COUNTRY
ON TABLE SET PAGE NOLEAD
END
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
December 03, 2014, 04:19 PM
j.gross
Notes:
I assume the intention is to align the alpha string produced with accompanying date-time fields (e.g., "start_time OVER end_time OVER elapsed"). -- If so, need to change || to |.
Here's code to do the same, but suppressing leading zero-valued components (so a 3.456 second elapsed time will display as "3.456", rjsf, rather than "000 00:00:03.456".)
DEFINE FUNCTION HH2ELAPSED23(FROM_TS/HYYMDs, TO_TS/HYYMDs)
D_DIFF/D12.2 = HDIFF(TO_TS, FROM_TS, 'MILLISECONDS', 'D12.2');
D_DAYS/I2L = INT(D_DIFF / 86400000);
D_HOURS/I2L = INT((D_DIFF - (D_DAYS * 86400000)) / 3600000);
D_MINS/I2L = INT((D_DIFF - (D_DAYS * 86400000) - (D_HOURS * 3600000)) / 60000);
D_SECS/I2L = INT((D_DIFF - (D_DAYS * 86400000) - (D_HOURS * 3600000) - (D_MINS * 60000)) / 1000);
D_MSECS/I3L = INT((D_DIFF - (D_DAYS * 86400000) - (D_HOURS * 3600000) - (D_MINS * 60000) - (D_SECS * 1000)));
HH2ELAPSED23/A23 =
IF D_DIFF EQ 0 THEN ' '
ELSE IF D_DAYS NE 0 THEN ' '|FPRINT(D_DAYS,'I3','A3')|' '|FPRINT(D_HOURS,'I2L','A2')|':'|FPRINT(D_MINS,'I2L','A2')|':'|FPRINT(D_SECS,'I2L','A2')|'.'|FPRINT(D_MSECS,'I3L','A3')
ELSE IF D_HOURS NE 0 THEN ' ' |FPRINT(D_HOURS,'I2' ,'A2')|':'|FPRINT(D_MINS,'I2L','A2')|':'|FPRINT(D_SECS,'I2L','A2')|'.'|FPRINT(D_MSECS,'I3L','A3')
ELSE IF D_MINS NE 0 THEN ' ' |FPRINT(D_MINS,'I2L','A2')|':'|FPRINT(D_SECS,'I2L','A2')|'.'|FPRINT(D_MSECS,'I3L','A3')
ELSE ' ' |FPRINT(D_SECS,'I2' ,'A2')|'.'|FPRINT(D_MSECS,'I3L','A3')
END
- Jack Gross WF through 8.1.05
December 08, 2014, 03:24 PM
DavSmith
Great piece of code Waz. My only suggestion, for displaying the zero padded TIME only, is to wrap the C_DIFF with FPRINT.
Jack, to remove the leading zeros from the hour minute second componants, I've modified your code by adding onto the Defined function a few lines that simplify.
The following code shows both padded and non-padded using OVER and right justifying both strings:
-* Display Elapsed time with zero padding
DEFINE FUNCTION HTIMEDIF(ENDTIME/HYYMDs, BEGTIME/HYYMDs)
DT_NULL/HYYMDs = HINPUT(14, '00010101000000000', 8, 'HHIS');
HTIMEDIF/HYYMDs = HADD(DT_NULL, 'MILLISECONDS',HDIFF(ENDTIME, BEGTIME, 'MILLISECONDS', 'D12.2'), 8, 'HYYMDs');
END
-* Display Elapsed time with zero padding removed
DEFINE FUNCTION HTIMEDFA(ENDTIME/HYYMDs, BEGTIME/HYYMDs)
DT_NULL/HYYMDs = HINPUT(14, '00010101000000000', 8, 'HYYMDs');
HTIMEDIF/HYYMDs = HADD(DT_NULL, 'MILLISECONDS',HDIFF(ENDTIME, BEGTIME, 'MILLISECONDS', 'D12.2'), 8, 'HYYMDs');
HRI /I2 =HPART(HTIMEDIF,'hh','I2');
MNI /I2 =HPART(HTIMEDIF,'mi','I2');
SCI /I2 =HPART(HTIMEDIF,'ss','I2');
MSI /I3 =HPART(HTIMEDIF,'ms','I3');
HRA /A3 =IF HRI EQ 0 THEN '' ELSE FPRINT(HRI,'I2','A2')||':';
MNA /A3 =IF HRI+MNI EQ 0 THEN '' ELSE FPRINT(MNI,'I2','A2')||':';
SCA /A3 =IF HRI+MNI+SCI EQ 0 THEN '' ELSE FPRINT(SCI,'I2','A2');
MSA /A4 =IF HRI+MNI+SCI+MSI EQ 0 THEN '0' ELSE '.'||EDIT(MSI) ;
HTIMEDFA/A12=STRIP(13,HRA||MNA||SCA||MSA,' ','A12');
END
DEFINE FILE CAR
BEG_TIME/HYYMDs = HINPUT(23, '2014/12/01 08:55:42.925', 8, 'HYYMDs');
END_TIME/HYYMDs = HINPUT(23, '2014-12-01 10:55:43.345', 8, 'HYYMDs');
END
TABLE FILE CAR
SUM BEG_TIME OVER
END_TIME OVER
COMPUTE C_DIFF1/A12 = FPRINT(HTIMEDIF(END_TIME, BEG_TIME),'HHISs','A12'); AS 'Elapsed' OVER
COMPUTE C_DIFF2/A12 = HTIMEDFA(END_TIME, BEG_TIME); AS 'Elapsed'
BY COUNTRY AS ''
WHERE COUNTRY EQ 'ENGLAND'
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
TYPE=DATA,JUSTIFY=RIGHT,$
ENDSTYLE
END