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
-
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.
0
Answers
-
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.
0 -
@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/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!**0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive