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:

Start:   2014-12-01 09:12:34.100
Finish:  2014-12-01 13:42:44.345
Elapsed:            04:30:10.245

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>,

Are these in a date time field ?

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.

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 -

DEFINE FUNCTION HTIMEDIFF(ENDTIME/HYYMDs, BEGTIME/HYYMDs)
  D_DIFF/D12.2  = HDIFF(ENDTIME, BEGTIME, '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)));
  HTIMEDIFF/A23 = '        '||EDIT(D_DAYS)||' '|EDIT(D_HOURS)||':'||EDIT(D_MINS)||':'||EDIT(D_SECS)||'.'||EDIT(D_MSECS);
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/A23 = HTIMEDIFF(END_TIME, BEG_TIME);
   BY COUNTRY
ON TABLE SET PAGE NOLEAD
END

T

@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

Actually, defining years and months elapsed gets dicey, since months vary in length, so
"ddd hh:mi:ss.mmm"
seems just right.

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

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
  
  

  
  

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

Output:

ENGLAND BEG_TIME 2014/12/01 08:55:42.925 
	END_TIME 2014/12/01 10:55:43.345 
	Elapsed             02:00:00.420 
	Elapsed		       2:0:0.420

Edited: Reviewed this after 3 years and realized I had the wrong BEG_TIME input in the DEFINE.

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