Originally posted by: denzer

Just wondering if anyone has an elapsed time routine they would be willing to share. It seems like it would be a common thing that’s been done many times before so before I do it again; searching the forum for elapsed time got me nothing, I thought I would ask.

Version: 8.2.0.4 - Build id: 77
I have 2 timestamps such as:
<startTime>2010-02-17 09:31:56.326</startTime>
<endTime>2010-02-17 09:32:03.355</endTime>
and I need to calculate the elapsed time between the two.

Thanks
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: repanzer

Have you tried;

datetimestampfield1 - datetimestampfield2
??

Thats field minus field.
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: denzer

datetimestampfield1 - datetimestampfield2 ?? would be:

Thanks, but that doesn’t work. Using the same time from my sample (with separators removed)

20100217093203355 - 20100217093156326 = 47029 or 47.029 seconds which is not correct because TX is doing a straight mathematical, not date arithmetic.

The answer I need to get to is the actual time difference of 7.029 seconds since there are only 60 minutes in a second and 60 seconds in an hour.
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: repanzer

I was going to say use ADDHOURS(date_1, -date_2)

That's a negative on the second value.
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Here you go:

=NUMBERTOTEXT((( TEXTTONUMBER( WORD( TEXT( EndTime Field ), ":", 1 ) ) - TEXTTONUMBER( WORD( TEXT( StartTime Field ), ":", 1 ) ) ) * 3600 ) + /* Hours */
( ( TEXTTONUMBER( WORD( TEXT( EndTime Field ), ":", 2 ) ) - TEXTTONUMBER( WORD( TEXT( StartTime Field ), ":", 2 ) ) ) * 60 ) + /* Minutes */
( TEXTTONUMBER( WORD( TEXT( EndTime Field ), ":", 3 ) ) - TEXTTONUMBER( WORD( TEXT( StartTime Field ), ":", 3 ) ) ) ) /* SS.mmm */

Kind regards,
~Danielle
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: denzer

Thanks Danielle. That works wonderfully and I was able to work on more important business rule coding and not have to re-invent the wheel.

You made my day.
Garrett
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: SystemAdmin

i am afraid but when we change the hr , its giving erroneous results.
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: JGibby

I don't mean to throw cold water on Danielle's formula, but unless I'm mistaken it will not give you accurate results when the start/end times cross date boundaries. Actually, Danielle's formula is similar to mine, in that it is based on converting a time to seconds. So her's will work perfectly as long as the end time is greater than the start time, but will give erroneous results if start time is from a previous date.

To set this up, my Start/End time fields are text fields with a full date time with seconds carried out to two decimal places in the format "CCYYMMDDHH24MMSS.2-2". I convert the end and start times individually to seconds, while adding 86400 (seconds in a day) to th end time for the difference of each date between the start and end times. Then a subtract the start seconds from the end seconds and you'll get the proper result.

=NUMBERTOTEXT(
( // EndTime
(
(
DATETONUMBER(TEXTTODATE(LEFT(EndTimeStamp Field,8)))
- // Minus
DATETONUMBER(TEXTTODATE(LEFT(StartTimeStamp Field,8)))
) * 86400
)
+TEXTTONUMBER(MID(EndTimeStamp Field, 9,2))*3600
+TEXTTONUMBER(MID(EndTimeStamp Field,11,2))*60
+TEXTTONUMBER(MID(EndTimeStamp Field,13,5))
)
- // Minus
( // StartTime
+TEXTTONUMBER(MID(StartTimeStamp Field, 9,2))*3600
+TEXTTONUMBER(MID(StartTimeStamp Field,11,2))*60
+TEXTTONUMBER(MID(StartTimeStamp Field,13,5))
)
)
John
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: JGibby

My bad, I should have previewed my message to include the formula in code brackets.

=NUMBERTOTEXT( ( 
// EndTime ( ( DATETONUMBER(TEXTTODATE(LEFT(EndTimeStamp Field,8))) - 
// Minus DATETONUMBER(TEXTTODATE(LEFT(StartTimeStamp Field,8))) ) * 86400 ) +TEXTTONUMBER(MID(EndTimeStamp Field, 9,2))*3600 +TEXTTONUMBER(MID(EndTimeStamp Field,11,2))*60 +TEXTTONUMBER(MID(EndTimeStamp Field,13,5)) ) - 
// Minus ( 
// StartTime +TEXTTONUMBER(MID(StartTimeStamp Field, 9,2))*3600 +TEXTTONUMBER(MID(StartTimeStamp Field,11,2))*60 +TEXTTONUMBER(MID(StartTimeStamp Field,13,5)) ) )

#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: denzer

First of all, thank you to everyone who provided feed back. However, for my purposes I went with what I hope is a much more robust calculator, here is what I settled on:

= /* This is the elapsed time in days~hours~minutes~seconds in a text field. You can then use the word command to extract and convert to seconds as needed. The timestamp inputs must be CCYYMMDDHH24MMSS3-3 in the start and end text fields. */

NumberToText ( //days
If (Mid (StartTS:Out, 9, 9) <= Mid (EndTS:Out, 9, 9) //Determining if 24 hours has passed?
, DateToNumber (TextToDate (Left (EndTS:Out, 8))) - DateToNumber (TextToDate (Left (StartTS:Out, 8)))
, DateToNumber (TextToDate (Left (EndTS:Out, 8))) - 1 - DateToNumber (TextToDate (Left (StartTS:Out, 8)))
))
+ "~" //hours
+ NumberToText (
If (Mid (StartTS:Out, 9, 9) <= Mid (EndTS:Out, 9, 9) //Across days check
, If (Mid (StartTS:Out, 11, 7) <= Mid (EndTS:Out, 11, 7) //Does the period of minutes within constitute a full hour?
, TextToNumber (Mid (EndTS:Out, 9, 2)) - TextToNumber (Mid (StartTS:Out, 9, 2))
, TextToNumber (Mid (EndTS:Out, 9, 2)) - 1 - TextToNumber (Mid (StartTS:Out, 9, 2)))
, If (Mid (StartTS:Out, 11, 7) <= Mid (EndTS:Out, 11, 7) //Does the period of minutes within constitute a full hour?
, TextToNumber (Mid (EndTS:Out, 9, 2)) + 24 - TextToNumber (Mid (StartTS:Out, 9, 2))
, TextToNumber (Mid (EndTS:Out, 9, 2)) + 24 - 1 - TextToNumber (Mid (StartTS:Out, 9, 2)))
))
+ "~" //minutes
+ NumberToText (
If (Mid (StartTS:Out, 11, 7) <= Mid (EndTS:Out, 11, 7) //Across hours check
, If (Mid (StartTS:Out, 13, 5) <= Mid (EndTS:Out, 13, 5) //Does the period of seconds within constitute a full minute?
, TextToNumber (Mid (EndTS:Out, 11, 2)) - TextToNumber (Mid (StartTS:Out, 11, 2))
, TextToNumber (Mid (EndTS:Out, 11, 2)) - 1 - TextToNumber (Mid (StartTS:Out, 11, 2)))
, If (Mid (StartTS:Out, 13, 5) <= Mid (EndTS:Out, 13, 5)
, TextToNumber (Mid (EndTS:Out, 11, 2)) + 60 - TextToNumber (Mid (StartTS:Out, 11, 2))
, TextToNumber (Mid (EndTS:Out, 11, 2)) + 60 - 1 - TextToNumber (Mid (StartTS:Out, 11, 2)))
))
+ "~" // seconds
+ NumberToText (
If (Mid (StartTS:Out, 13, 5) <= Mid (EndTS:Out, 13, 5) //Across minutes check
, TextToNumber (Mid (EndTS:Out, 13, 5)) - TextToNumber (Mid (StartTS:Out, 13, 5))
, TextToNumber (Mid (EndTS:Out, 13, 5)) + 60000 - TextToNumber (Mid (StartTS:Out, 13, 5))
) / 1000)
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: john.gibby

Denzer, I did like you approach to each element of the time (hours, minutes & seconds) instead of my approach to just converting everything to seconds. The reason being that converting a lump sum of seconds is somewhat cumbersome to reconvert to hours, minutes & seconds. I had a place where I wanned to show hh:mm:ss.000 and used your formula, but the seconds showed up without leading zeros if they were less than a single second. (i.e. 10:11:07.079 - 10:11:07.057 = 0~0~.052). So I modified your formula to address this and to not show the elapsed days as days but include it in the time (i.e. over one day would be more than 24 hours). Thanks for getting me thinking of a different way of calculating the time!

John Gibby

// Hours ------------------------------------------------------------------------------ =FILLLEFT( NUMBERTOTEXT( ( 
// Hours from the number of elapsed days ( TEXTTONUMBER(MID(LEAVENUM(EndTime),1,8)) - TEXTTONUMBER(MID(LEAVENUM(StartTime),1,8)) 
//Determining IF 24 hours has passed? - IF(MID(LEAVENUM(EndTime),9,9) < MID(LEAVENUM(StartTime),9,9),1,0) ) * 24 ) + TEXTTONUMBER(MID(LEAVENUM(EndTime),9,2)) - TEXTTONUMBER(MID(LEAVENUM(StartTime),9,2)) 
// Cross days check / borrow 24 hours from days + IF(MID(LEAVENUM(EndTime),9,10) < MID(LEAVENUM(StartTime),9,10),24,0) 
// Cross hours check / will need to loan 1 hour to minutes calc - IF(MID(LEAVENUM(EndTime),11,8) < MID(LEAVENUM(StartTime),11,8),1,0) ) ,
"0" ,2 ) + 
":" 
// Minutes ------------------------------------------------------------------------------ +FILLLEFT( NUMBERTOTEXT( TEXTTONUMBER(MID(LEAVENUM(EndTime),11,2)) - TEXTTONUMBER(MID(LEAVENUM(StartTime),11,2)) 
// Cross minutes check / borrow 60 minutes from hours + IF(MID(LEAVENUM(EndTime),11,6) < MID(LEAVENUM(StartTime),11,6),60,0) 
// Cross hours check / will need to loan 1 hour to seconds - IF(MID(LEAVENUM(EndTime),13,8) < MID(LEAVENUM(StartTime),13,8),1,0) ) ,
"0" ,2 ) + 
":" 
// Seconds ------------------------------------------------------------------------------ + FILLLEFT( FROMNUMBER( (TEXTTONUMBER(MID(LEAVENUM(EndTime),13,5)) / 1000) - (TEXTTONUMBER(MID(LEAVENUM(StartTime),13,5)) / 1000) 
// Cross minutes check / borrow 60 seconds from minutes + IF(MID(LEAVENUM(EndTime),13,5) < MID(LEAVENUM(StartTime),13,5),60,0) ,
"{####'.'3##3}" ) ,
"0" ,6 )

#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange