Time difference in decimal format.

[andriesb]

I have two day time values within a database and want to create a decimal calculated value as a new field within my script to show the total hours in a decimal format.

time1   20120815153000

time2   20120815070000

Format:YYYYMMDDHHMMSS

Within this example my end time is 15h30 and my starting time is 07h30. I would like to have an integer value of 8.

A second attempt would be to also use the amount of day's:

(Left(Dte,8) - Left(Dts,8) + 1) * ((Dte - Dts)/100)

AS IZ.TotaalHours,

This works except for the hour part.

[swuehl]

First, inteprete your datetime values correctly using e.g. timestamp#() function.

Then you can calculate your time difference using interval() function.

LOAD *,

     num#(Interval(time1-time2,'h')) as DurationHours;

LOAD timestamp(Timestamp#(time1,'YYYYMMDDhhmmss')) as time1,

     timestamp(Timestamp#(time2,'YYYYMMDDhhmmss')) as time2

INLINE [

time1, time2  

20120815153000,20120815070000

]

[andriesb · Accepted solution]

I actually managed to solve my problem in a rather easy way with your suggested timestamp:

This could be done within the original scripting without an extra load.

Round(( Timestamp#(time2,'yyyymmddhhmmss') - Timestamp#(time1,'yyyymmddhhmmss')) * 24, 0.1)

This will format my times, will subtract them and convert them into a number: multiplication with 24 will effectly resolve it into a needed decimal number.

As these are quite large numbers, the round function for this example will round the results into the needed 8,5 hours.

A second stage could be to calculate the amount of days. using ony the 8 left characters and multiply this with the hours / day.

[swuehl]

Just reuse the timestamp difference (the integer part is giving the number of days):

Round(( Timestamp#(time2,'yyyymmddhhmmss') - Timestamp#(time1,'yyyymmddhhmmss')) )

to get the number of days rounded or

floor(( Timestamp#(time2,'yyyymmddhhmmss') - Timestamp#(time1,'yyyymmddhhmmss')) )

to get the number of days, only full days counted.