Time Difference larger than 24 hours

user12621

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?

Find more posts tagged with

Accepted answers

user12621

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.

All comments

user12621

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.

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

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

user12621

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

jaeW_at_Onyx

format it.

user12621

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

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

GrantSmith

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.