WebFOCUS Display difference between 2 date-time values as # Days, # Hours, # Minutes, #Seconds

[lisa.cu]

Is there a formula/function in WebFocus that allows you to compare two date-time fields and display the difference in business days, hours, minutes and seconds (similar to how the results might look using Excel formula).

I am very new to this tool and my client wants to see the difference like this for example: 44 Days, 3 Hours, 20 Minutes, 57 Seconds or 44:03:20:57

Here are some that I've tried per the WebFocus Using Functions Release 8.2 Version 03 and Higher guide, but not getting me closer to what I need it to do.

DATEDIF ( "Current DateTime" , "DueDateTime" , 'BD' )

HDIFF ( "CurrentDateTime" , "DueDateTime" , 'DAY' , 'D12.2' ) (does not account for business days)

Maybe it's not possible in this tool. Thanks for any help!

[David Beagan]

How are business hours (or minutes or seconds) defined? For example, you might decide that business hours run from 9am to 5pm Monday through Friday. Are holidays counted?

It could be done, but it could get complicated depending on the answers to the above. 

[lisa.cu]

Thanks for your reply. That's exactly what they are requesting - business hours run from 9am to 5pm Monday through Friday, with holidays also excluded. Which I hope will be read by the holiday file in WebFocus.  Minutes and seconds are a 'nice to have'. I think I could get them to accept the business days/hours at a minimum.

 

Edited March 8 by lisa.cu

[David Beagan]

This can be accomplished by calculating the total number of seconds in three different parts.
   1. The number of seconds for the beginning day.   
       (using the DATEDIF() function)

   2. Number of days between beginning and ending days and multiplying by 28800 seconds in a day.
        (using the DTDIFF() function)

   3. Number of seconds for the ending day.            
        (using the DATEDIF() function) 

   4. Add up the above three and reformat these total seconds into a dd:hh:mm:ss diaplay.

I will see what I can come up with to do this.

Edited March 10 by David Beagan

[David Beagan]

This BUSDIFF() function defined below I think accomplishes what you are asking for. It uses two additional functions, HDMS() to convert seconds into dd:hh:mm:ss and WORKDAY() that returns a 1 if the date is a workday, 0 if it is not. 
The WebFOCUS DTDIFF() function includes the start day as part of its counting, but not the last day.
That is why there is a +1 in the start date (but not the end day) for the DATEDIF() function in calculating DAYS.
Note the functions switch the date order DATEDIF(Start,End,) but DTDIFF(End,Start,).

DEFINE FUNCTION DHMS(SECONDS/P20)
DAY/I2   = SECONDS/28800;
HR/I2    = MOD(SECONDS/3600, 8);
MIN/I2   = MOD(SECONDS/60, 60);
SEC/I2   = MOD(SECONDS, 60);
DHMS/A20 = EDIT(DAY) |':'| EDIT(HR) |':'| EDIT(MIN) |':'| EDIT(SEC);
END

DEFINE FUNCTION WORKDAY(DATETIME/HYYMDS)
YYMD1/YYMD = DATETIME;
WORKDAY/I1 = DATEDIF( YYMD1, YYMD1 + 1, 'BD');
END

DEFINE FUNCTION BUSDIFF(DT1/HYYMDS,DT2/HYYMDS)
  YMD1/YYMD     = DT1;
  YMD2/YYMD     = DT2;
  DAYS/I11C     = DATEDIF(YMD1 + 1, YMD2, 'BD');
  A8YMD1/A8YYMD = YMD1;
  DT5PM/HYYMDS  = HINPUT(17, A8YMD1|'170000', 8, 'HYYMDs');
  DIFFSECS1/I9C = DTDIFF(DT5PM, DT1, SECOND);
  SECONDS1/I9C  = MAX(0, MIN(DIFFSECS1,28800) );
  A8YMD2/A8YYMD = YMD2;
  DT9AM/HYYMDS  = HINPUT(17, A8YMD2|'090000', 8, 'HYYMDs');
  DIFFSECS2/I9C = DTDIFF(DT2, DT9AM, SECOND);
  SECONDS2/I9C  = MAX(0, MIN(DIFFSECS2,28800) );
  SECONDS/I9C   = SECONDS1 * WORKDAY(DT1) + DAYS * 28800 + SECONDS2 * WORKDAY(DT2);
  BUSDIFF/A20   = DHMS(SECONDS);
END

TABLE FILE systable
PRINT NAME NOPRINT
COMPUTE DATE1/HYYMDS = '2024/03/07 13:12:34';
COMPUTE DATE2/HYYMDS = '2024/03/12 12:32:40';
COMPUTE TIME/A20     = BUSDIFF(DATE1,DATE2);
ON TABLE SET PAGE NOLEAD
WHERE READLIMIT IS 1
END 

You should be able to run this code in your environment to try it out.

 

Edited March 11 by David Beagan

[lisa.cu]

This is great, I am going to test this in my environment. Thank you

[David Beagan]

Here’s a suggestion for how you might want to implement this. After you have created defined functions that are generally useful, you wouldn’t want to repeat the define function code in every fex where you use it. So you would reasonably end up putting functions in a fex file and -INCLUDE wherever needed. But I think another approach might even be cleaner.
In the Describing Data https://docs.tibco.com/pub/wf-wf/9.2.3/doc/pdf/IBI_wf-wf_9.2.3_dd_language.pdf#page=216 manual, page 216 shows how to reference a DEFINE FUNCTION in an expression in a Master File DEFINE, COMPUTE, or FILTER field. 
   DF.funcfile.defunc(parm1,parm2,...)

I can’t find documentation that this feature is available to be used in reporting situations. But I tried it and it works. The file containing the defined functions needs to be a fex file in an app folder such as baseapp.
For example: 

baseapp/functions.fex
DEFINE FUNCTION DHMS(SECONDS/P20)
DAY/I2   = SECONDS/28800;
HR/I2    = MOD(SECONDS/3600, 8);
MIN/I2   = MOD(SECONDS/60, 60);
SEC/I2   = MOD(SECONDS, 60);
DHMS/A20 = EDIT(DAY) |':'| EDIT(HR) |':'| EDIT(MIN) |':'| EDIT(SEC);
END
 
DEFINE FUNCTION WORKDAY(DATETIME/HYYMDS)
YYMD1/YYMD = DATETIME;
WORKDAY/I1 = DATEDIF( YYMD1, YYMD1 + 1, 'BD');
END
 
DEFINE FUNCTION BUSDIFF(DT1/HYYMDS,DT2/HYYMDS)
  YMD1/YYMD     = DT1;
  YMD2/YYMD     = DT2;
  DAYS/I11C     = DATEDIF(YMD1 + 1, YMD2, 'BD');
  A8YMD1/A8YYMD = YMD1;
  DT5PM/HYYMDS  = HINPUT(17, A8YMD1|'170000', 8, 'HYYMDs');
  DIFFSECS1/I9C = DTDIFF(DT5PM, DT1, SECOND);
  SECONDS1/I9C  = MAX(0, MIN(DIFFSECS1,28800) );
  A8YMD2/A8YYMD = YMD2;
  DT9AM/HYYMDS  = HINPUT(17, A8YMD2|'090000', 8, 'HYYMDs');
  DIFFSECS2/I9C = DTDIFF(DT2, DT9AM, SECOND);
  SECONDS2/I9C  = MAX(0, MIN(DIFFSECS2,28800) );
  SECONDS/I9C   = SECONDS1 * DF.functions.WORKDAY(DT1) + DAYS * 28800 + SECONDS2 * DF.functions.WORKDAY(DT2);
  BUSDIFF/A20   = DF.functions.DHMS(SECONDS);
END

The BUSDIFF() function’s references to other functions in the code abve uses the similar syntax, for example:  DF.functions.DHMS(SECONDS) 

Now the report just needs to reference the function.

TABLE FILE systable
PRINT NAME NOPRINT
COMPUTE DATE1/HYYMDS = '2024/03/07 13:12:34';
COMPUTE DATE2/HYYMDS = '2024/03/12 12:32:40';
COMPUTE TIME/A20     = DF.functions.BUSDIFF(DATE1,DATE2);
ON TABLE SET PAGE NOLEAD
WHERE READLIMIT IS 1
END