Time Difference larger than 24 hours

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 ⚪️
    Accepted 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