Time Difference larger than 24 hours

user12621
user12621 Member
in Magic ETL

I have two date columns, and I want to find the time difference between them.

Sometimes the differences between the times is larger than 24 hours.

How can I get the entire time difference, making sure to also take the difference of date into account?

 

In beast mode, TIMEDIFF(col1,col2) looks only at the literal time, and does not take date into consideration.

Example:

start = 2021-01-01 08:00:00,

end = 2021-01-02 09:00:00,

TIMEDIFF(end,start) output =  01:00:00.

However, my desired output would be 25:00:00, by taking both date and time into account.

 

Is there any method for getting my desired output in beast mode?

Best Answer

  • user12621
    user12621 Member
    Answer ✓

    I've managed to clump together a solution for this. It's a bit of a mess to look at though.

     

    First I find the number of days difference, and convert it into seconds (86400 seconds = 1 day).

    Then add the time difference, also converted to seconds.

    After finding the time difference in seconds I divide by 3600 (60*60) to get the time difference in decimal hours.

     

    ((
(86400*DATEDIFF(`enddate`,`startdate`))+
(time_to_sec(TIMEDIFF(`enddate`,`startdate`)))
)/60)/60

     

     

    Now to convert decimal hours to hh:mm:ss.

    Replace all instances of 'timecol' in the concat below with whatever you got from the script above.

    The cases concat a 0 to the start of each segment if it's value is below 10. This is done so times are displayed as 01:02:03 instead 1:2:3.

    The rounds are done to get each individual unit of the time from the decimal hours. (I took this part from another thread on the domo dojo but I can't remember where I got it from, sorry not giving credit!)

     

    CONCAT(
case when ROUND(timecol - 0.5, 0) <= 9 then '0' else '' end,
ROUND(timecol - 0.5, 0), 
':', 
case when ROUND((timecol - ROUND(timecol - 0.5))*60-0.5, 0) <= 9 then '0' else '' end,
ROUND((timecol - ROUND(timecol - 0.5))*60-0.5, 0), 
':', 
case when ROUND(((timecol - ROUND(timecol - 0.5))*60 - ROUND((timecol - ROUND(timecol - 0.5))*60 - 0.5, 0))*60, 0) <= 9 then '0' else '' end,
ROUND(((timecol - ROUND(timecol - 0.5))*60 - ROUND((timecol - ROUND(timecol - 0.5))*60 - 0.5, 0))*60, 0)
)

     

     

    So if you replace all the timecols it will look like this:

    CONCAT(
case when ROUND((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - 0.5, 0) <= 9 then '0' else '' end,
ROUND((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - 0.5, 0), 
':', 
case when ROUND(((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - ROUND((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - 0.5))*60-0.5, 0) <= 9 then '0' else '' end,
ROUND(((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - ROUND((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - 0.5))*60-0.5, 0), 
':', 
case when ROUND((((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - ROUND((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - 0.5))*60 - ROUND(((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - ROUND((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - 0.5))*60 - 0.5, 0))*60, 0) <= 9 then '0' else '' end,
ROUND((((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - ROUND((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - 0.5))*60 - ROUND(((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - ROUND((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - 0.5))*60 - 0.5, 0))*60, 0)
)

     

    Hopefully this is useful to someone else and saves you the hours I spent fumbling this together.

Answers

  • user12621
    user12621 Member
    Answer ✓

    I've managed to clump together a solution for this. It's a bit of a mess to look at though.

     

    First I find the number of days difference, and convert it into seconds (86400 seconds = 1 day).

    Then add the time difference, also converted to seconds.

    After finding the time difference in seconds I divide by 3600 (60*60) to get the time difference in decimal hours.

     

    ((
(86400*DATEDIFF(`enddate`,`startdate`))+
(time_to_sec(TIMEDIFF(`enddate`,`startdate`)))
)/60)/60

     

     

    Now to convert decimal hours to hh:mm:ss.

    Replace all instances of 'timecol' in the concat below with whatever you got from the script above.

    The cases concat a 0 to the start of each segment if it's value is below 10. This is done so times are displayed as 01:02:03 instead 1:2:3.

    The rounds are done to get each individual unit of the time from the decimal hours. (I took this part from another thread on the domo dojo but I can't remember where I got it from, sorry not giving credit!)

     

    CONCAT(
case when ROUND(timecol - 0.5, 0) <= 9 then '0' else '' end,
ROUND(timecol - 0.5, 0), 
':', 
case when ROUND((timecol - ROUND(timecol - 0.5))*60-0.5, 0) <= 9 then '0' else '' end,
ROUND((timecol - ROUND(timecol - 0.5))*60-0.5, 0), 
':', 
case when ROUND(((timecol - ROUND(timecol - 0.5))*60 - ROUND((timecol - ROUND(timecol - 0.5))*60 - 0.5, 0))*60, 0) <= 9 then '0' else '' end,
ROUND(((timecol - ROUND(timecol - 0.5))*60 - ROUND((timecol - ROUND(timecol - 0.5))*60 - 0.5, 0))*60, 0)
)

     

     

    So if you replace all the timecols it will look like this:

    CONCAT(
case when ROUND((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - 0.5, 0) <= 9 then '0' else '' end,
ROUND((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - 0.5, 0), 
':', 
case when ROUND(((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - ROUND((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - 0.5))*60-0.5, 0) <= 9 then '0' else '' end,
ROUND(((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - ROUND((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - 0.5))*60-0.5, 0), 
':', 
case when ROUND((((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - ROUND((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - 0.5))*60 - ROUND(((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - ROUND((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - 0.5))*60 - 0.5, 0))*60, 0) <= 9 then '0' else '' end,
ROUND((((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - ROUND((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - 0.5))*60 - ROUND(((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - ROUND((((86400*DATEDIFF(`enddate`,`startdate`))+(time_to_sec(TIMEDIFF(`enddate`,`startdate`))))/60)/60 - 0.5))*60 - 0.5, 0))*60, 0)
)

     

    Hopefully this is useful to someone else and saves you the hours I spent fumbling this together.

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @GrantSmith has a great writeup somewhere (grant, can you link it?) 

     

    wherein he recommends converting your dateTime collumns using unix_timestamp, which should convert the dateTime into a number of seconds (which you can absolutely add or subtract).  Then you just divide it by 60 or 3600 etc. to get number of minutes ,hours or seconds.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • user12621
    user12621 Member

    wouldn't this still only be in decimal, and not hhmmss format ?

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    format it.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • user12621
    user12621 Member

    Could you please show me how to format from unix time into hh:mm:ss?
    I'm still running into the same issue of not being able to show times larger than 24 hours.

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    https://stackoverflow.com/questions/41330789/convert-seconds-to-days-hours-minutes-seconds-mysql

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • GrantSmith
    GrantSmith Coach

    Hi @user12621 

     

    I like to use the UNIX_TIMESTAMP function on both timestamps and some simple math to determine the number of seconds between the two different timestamps.

     

    I wrote a prior answer on this and how to format it into days hours minutes and seconds.

     

    https://dojo.domo.com/t5/Dataflows/Date-Calculations-minutes-and-seconds/m-p/49240

     

    For clarification you can replace the `Date` in that example with your `start_date` field and CURRENT_TIMESTAMP() with your `end_date` field.

     

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories