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 = 20210101 08:00:00,
end = 20210102 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

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))*600.5, 0) <= 9 then '0' else '' end, ROUND((timecol  ROUND(timecol  0.5))*600.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))*600.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))*600.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.
0
Answers

@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"0 
wouldn't this still only be in decimal, and not hhmmss format ?
0 
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"0 
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.0 
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"0 
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/DateCalculationsminutesandseconds/mp/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!**0
Categories
 11.3K All Categories
 6 Private Company Board
 2 APAC User Group
 12 Welcome
 45 Domo News
 10.2K Using Domo
 2K Dataflows
 2.6K Card Building
 2.3K Ideas Exchange
 1.2K Connectors
 353 Workbench
 269 Domo Best Practices
 13 Domo Certification
 481 Domo Developer
 58 Domo Everywhere
 112 Apps
 733 New to Domo
 86 Dojo
 Domopalooza
 1.1K 日本支部
 4 道場日本支部へようこそ
 31 お知らせ
 65 Kowaza
 305 仲間に相談
 667 ひらめき共有