Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

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


lisa.cu

Recommended Posts

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!

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

Posted (edited)

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 by lisa.cu
Link to comment
Share on other sites

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 by David Beagan
Link to comment
Share on other sites

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 by David Beagan
Link to comment
Share on other sites

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 

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...