Delta time in decimal not working in a load

[#1]

Hi,

I have some trouble to display a delta time in decimal

I have a table with an ID, a text, and 2 columns date (dateA, DateB)

I want to load this table and calculate the delta time between these 2 dates in decimal hour...

To do so, I made:

Table:

LOAD

     ID,

     concat(text, '/') as "text",

     sum(interval(DateA-DateB, 's') )as "Delta"

Resident Table GROUP BY ID;

My delta is in seconds but I can't convert it into decimal hour... When I try to put / 3600, the result is not as expected.

For example : Delta = 82500s, I should have 22.92 in Delta when I divide by 3600... But when I try to do sum(interval(DateA-DateB, 's') /3600), the result is 0.00265....

Does anyone have an idea ?

Thank you in advance for your reply

[swuehl]

That's because your Delta is only presenting the seconds in the text representation, while the internal data format is still representing time in fractions of days.

So you need to multiply by 24 instead of dividing by 3600!

edit:

Table:

LOAD

     ID,

     concat(text, '/') as "text",

     sum(DateA-DateB) * 24 as "Delta Hours"

Resident Table GROUP BY ID;

[CELAMBARASAN · Accepted solution]

You should try with

Sum(Num#(Text(interval(DateA-DateB, 's')), '#') /3600)

or

Num#(Text(Interval(Sum(Interval(DateA-DateB, 's')), 'hh')), '#')

[#4]

Perfect, thank you !