Differnce between two datetime values in HH:MM:SS format

Differnce between two datetime values in HH:MM:SS format

  
Hi,

I have a Begindatetime and EndDateTime in my query.I want to calculate the differnce b/w them and the result should be in HH:MM:SS format only.I have tried using DiffHours(),NewTime() built in functions but I am not getting correct result. Can anyone help on this.


Thanks,
Prashanth 
For a project I'm involved with I'll need the same sort of functionality so I figured I'd just knock this out for both of us.  The attached OML file has a simple screen with two datetime fields.  Anytime one of the fields is changed, the difference is recalculated.  It handles differences greater than 24 hours and negative differences.  If there is a possibility that one of the fields will be empty, you'll have to add some code to deal with that otherwise you get very high values because an empty date on the Outsystems platform is 1900-01-01.

The real trick is that you need to handle each part (hours, minutes and seconds) as a separate calculation then string the results together.  You'll see that if you look at the web screen action OnTimeData_TimeChange.

Hope this helps,
Curt
Curt Raddatz wrote:
For a project I'm involved with I'll need the same sort of functionality so I figured I'd just knock this out for both of us.  The attached OML file has a simple screen with two datetime fields.  Anytime one of the fields is changed, the difference is recalculated.  It handles differences greater than 24 hours and negative differences.  If there is a possibility that one of the fields will be empty, you'll have to add some code to deal with that otherwise you get very high values because an empty date on the Outsystems platform is 1900-01-01.

The real trick is that you need to handle each part (hours, minutes and seconds) as a separate calculation then string the results together.  You'll see that if you look at the web screen action OnTimeData_TimeChange.

Hope this helps,
Curt
 Hi Curt,

I am unable to open this espace.It throws an error to upgrade my version.Can you please provide some high level logic to implment this?

Thanks,
Prashanth
 
It was created on Version 8 so if you are on a lower version you'll get that error.   I'll try and explain the key parts here.

1) I created an Entity called TimeData with two fields - BeginDateTime and EndDateTime - type is Date Time.  You should be able to use any two date time fields you want as long as you use the correct field names in the logic in step 6.
2) I created a web screen with an edit record widget for the table with these two fields.
3) Add three local variables to the web screen - HoursDiff, MinsDiff and SecsDiff - all integers
4) Add an expression widget to the page and call it TimeDifference - don't worry about the error for value - we will fix that later
5) Add a web screen action called OnTimeData_TimeChange
6) In the web screen action add an assignment to the flow and make the following three assignments
    a) HoursDiff = Trunc(DiffSeconds(TimeDataEdit.Record.TimeData.BeginDateTime, TimeDataEdit.Record.TimeData.EndDateTime) / 3600)
    b) SecsDiff = Mod(DiffSeconds(TimeDataEdit.Record.TimeData.BeginDateTime, TimeDataEdit.Record.TimeData.EndDateTime), 60)
    c) MinsDiff = Trunc((DiffSeconds(TimeDataEdit.Record.TimeData.BeginDateTime, TimeDataEdit.Record.TimeData.EndDateTime) - (HoursDiff * 3600) - SecsDiff) / 60)
7) Next add an Ajax refresh to the flow and set it to refresh TimeDifference
8) Go back to the web screen and click on the expression we added called TimeDifference and set the value to the following
    HoursDiff + ":" + FormatText(MinsDiff, 2, 2, True, "0") + ":" + FormatText(SecsDiff, 2, 2, True, "0")
9) For the BeginDateTime and EndDateTime fields, set the On Change destination to OnTimeData_TimeChange

That should get you going.

Curt

Curt Raddatz wrote:
It was created on Version 8 so if you are on a lower version you'll get that error.   I'll try and explain the key parts here.

1) I created an Entity called TimeData with two fields - BeginDateTime and EndDateTime - type is Date Time.  You should be able to use any two date time fields you want as long as you use the correct field names in the logic in step 6.
2) I created a web screen with an edit record widget for the table with these two fields.
3) Add three local variables to the web screen - HoursDiff, MinsDiff and SecsDiff - all integers
4) Add an expression widget to the page and call it TimeDifference - don't worry about the error for value - we will fix that later
5) Add a web screen action called OnTimeData_TimeChange
6) In the web screen action add an assignment to the flow and make the following three assignments
    a) HoursDiff = Trunc(DiffSeconds(TimeDataEdit.Record.TimeData.BeginDateTime, TimeDataEdit.Record.TimeData.EndDateTime) / 3600)
    b) SecsDiff = Mod(DiffSeconds(TimeDataEdit.Record.TimeData.BeginDateTime, TimeDataEdit.Record.TimeData.EndDateTime), 60)
    c) MinsDiff = Trunc((DiffSeconds(TimeDataEdit.Record.TimeData.BeginDateTime, TimeDataEdit.Record.TimeData.EndDateTime) - (HoursDiff * 3600) - SecsDiff) / 60)
7) Next add an Ajax refresh to the flow and set it to refresh TimeDifference
8) Go back to the web screen and click on the expression we added called TimeDifference and set the value to the following
    HoursDiff + ":" + FormatText(MinsDiff, 2, 2, True, "0") + ":" + FormatText(SecsDiff, 2, 2, True, "0")
9) For the BeginDateTime and EndDateTime fields, set the On Change destination to OnTimeData_TimeChange

That should get you going.

Curt
 
 Hi Curt,
Thanks for detailed steps.Just now I managed to get the required format(HH:MM:SS) as shown below.Let me know your view on this and suggest me if someting is worng in below code.

TextToTime(IntegerToText(Hour(AddSeconds(NullDate(),DiffSeconds(StartDateTime,EndDateTime))))+":"+
IntegerToText(Minute(AddSeconds(NullDate(),DiffSeconds(StartDateTime,EndDateTime))))+":"+
IntegerToText(Second(AddSeconds(NullDate(),DiffSeconds(StartDateTime,EndDateTime)))))

Thanks,
Prashanth
 
If it works for you that's all that matters.  It seems you're doing more calculations so it would be less efficient and more complicated than what I did.  Also, if the number of minutes or seconds is less than 10 you'll only get one digit to display.  I ran into that which is why I used FormatText instead of IntegerToText.