Calculated Field to Calculate Fraction of Hours between Two Timestamps

byusteve

I am trying to calculate the number of hours (w/fraction, e.g. 36.25) between two datetime stamps, one of these fields may or may not be populated. I have tried multiple "TIMEDIFF()" fuctions but I cannot simply get the number of hours between the two datetime stamps. I've come close but cannot seem to get negative hours (i.e. 12:00 - 16:00 = -4).

 

Both fields are recognized by Domo as a date. There are times when the two values may be the same, although my attempts to filter these out has been unsuccessful.

 

Any suggestions woudl be appreciated. I've been working on different approaches for over 2 hours. Thanks in advance!

 

Below is my code thus far, I think I am close...

 

CASE
 WHEN `start_datetime` IS NOT NULL
 AND `end_datetime` IS NOT NULL
 AND DATE_FORMAT(`end_datetime`, '%H:%i:%s') < DATE_FORMAT(`start_datetime`, '%H:%i:%s')
 THEN ((DATEDIFF(`end_datetime`, `start_datetime`) - 1)*24)
 + ROUND(TIME_TO_SEC(TIMEDIFF(`end_datetime`, `start_datetime`))/3600,2)
 WHEN `start_datetime` IS NOT NULL
 AND `end_datetime` IS NOT NULL
 AND DATE_FORMAT(`end_datetime`, '%H:%i:%s') > DATE_FORMAT(`start_datetime`, '%H:%i:%s')
 THEN ((DATEDIFF(`end_datetime`, `start_datetime`)*24)
 + ROUND(TIME_TO_SEC(TIMEDIFF(`end_datetime`, `start_datetime`))/3600,2)
END

 

byusteve

In case anyone is reading this down the road, I ended-up using "TO_SECONDS()" on both dates, calculating the difference, and then converting to hours. It was much cleaner.

 

TIMEDIFF() was behaving differently in BeastMode (was maxing out at 23:59:59) vs. a mySQL dataflow. Additionally by design (for some unknown reason) it maxes out at 838:59:59 (see http://www.mysqltutorial.org/mysql-timediff/), so if you are calculating differences that exceed ~35 days you'll run into problems with TIMEDIFF().

byusteve

This code seems to be working, although I have a little more testing to do (i.e. when the times are equal):

 

CASE
 WHEN `start_datetime` IS NOT NULL
 AND `end_datetime` IS NOT NULL
 THEN
 CASE
 WHEN DATE_FORMAT(`end_datetime`, '%H:%i:%s') < DATE_FORMAT(`start_datetime`, '%H:%i:%s')
 THEN ((DATEDIFF(`end_datetime`, `start_datetime`)-1)*24) +
 ROUND(TIME_TO_SEC(TIMEDIFF(`end_datetime`, `start_datetime`))/3600,2)
 ELSE
 (DATEDIFF(`end_datetime`, `start_datetime`)*24) +
 ROUND(TIME_TO_SEC(TIMEDIFF(`end_datetime`, `start_datetime`))/3600,2)
 END
END

byusteve

In case anyone is reading this down the road, I ended-up using "TO_SECONDS()" on both dates, calculating the difference, and then converting to hours. It was much cleaner.

 

TIMEDIFF() was behaving differently in BeastMode (was maxing out at 23:59:59) vs. a mySQL dataflow. Additionally by design (for some unknown reason) it maxes out at 838:59:59 (see http://www.mysqltutorial.org/mysql-timediff/), so if you are calculating differences that exceed ~35 days you'll run into problems with TIMEDIFF().

[Deleted User]

Thanks for sharing @byusteve!

user001271

I'm trying to do the same with the timestamps, however when the values are negative it is not substracting it properly. Any idea on how this can be solved?

e.g.

07 May 2018 16:00 - 08 May 2018 00:52 the result is -0.89 instead of -8.9hrs

Thanks,

Jessica.

JacobFolsom

The code below will produce negative fractional hours if Start Date > End Date and positive fractional hours if End Date > Start Date.

 

(UNIX_TIMESTAMP(`End Date`) - UNIX_TIMESTAMP(`Start Date`)) / 3600

NOTE: UNIX_TIMESTAMP() returns the number of seconds since 1970 and will therefore only work on dates after that time.