Calculated Field to Calculate Fraction of Hours between Two Timestamps

byusteve
byusteve Member
in Magic ETL

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

  • byusteve
    byusteve Member

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

  • [Deleted User]
    [Deleted User] Member

    Thanks for sharing @byusteve!

  • user001271
    user001271 Member

    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
    JacobFolsom Domo Employee

    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"

Categories