Calculated Field to Calculate Fraction of Hours between Two Timestamps

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

 

Best Answer

  • byusteve
    byusteve Member
    Answer ✓

    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().

Answers

  • 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
    byusteve Member
    Answer ✓

    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().

  • Thanks for sharing @byusteve!

  • 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.

  • 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.

     

    Jacob Folsom
    **Say “Thanks” by clicking the “heart” in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"