Beastmode - Duration (mins) between 2 Datetime Columns
Best Answers
-
The TIMEDIFF() function will get you the hh:mm:ss value between the two, and the TIME_TO_SEC() function will give you the number of seconds in time. Nest those two and divide by 60 seconds to get minutes. See if something like this will work:
TIME_TO_SEC(TIMEDIFF(`EndTime`,`StartTime`)) / 60
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"4 -
Try this. It works for me
NULLIF(DATEDIFF(`metric_set_initially_assigned_at`,`metric_set_created_at`),0)*24*60+NULLIF(HOUR(`metric_set_initially_assigned_at`-`metric_set_created_at`) ,0)*60
0
Answers
-
Have you tried TIMESTAMPDIFF(MINUTE, Col1, Col2)? That works in MySQL, not sure if it will in BeastMode though.
0 -
Or have you tried TIMESTAMP(Col2) - TIMESTAMP(Col1)? (Although some conversion may be necessary to get it into "minutes"...)
0 -
The TIMEDIFF() function will get you the hh:mm:ss value between the two, and the TIME_TO_SEC() function will give you the number of seconds in time. Nest those two and divide by 60 seconds to get minutes. See if something like this will work:
TIME_TO_SEC(TIMEDIFF(`EndTime`,`StartTime`)) / 60
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"4 -
I haven't. Is MySQL the same as Magic ETL? I tried to lookup a video on this but the video on domo university seems to be from an older version.
0 -
This worked, thanks.
0 -
Does this work for multiple days that have lapsed? The example I have is:
Start
4/1/2016 0:23
End
4/8/2016 11:45
When I use
TIME_TO_SEC(TIMEDIFF(`EndTime`,`StartTime`)) / 60
I don't get the expected results.
0 -
@pravaliya I haven't tried that before, but I would think so. But only up to a certain point. Time data types have a limit.
TIMEDIFF() will get you the HHH:MM:SS, like 179 hours, 22 min, 0 sec.
So theoretically I think it would calculate that out as (179:22:00 * 3600 s) / 60 s = 10762 minutes . What are you getting?
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
I've tried this and i seem to be running into an issue when the difference is greater than 24 hours. it only seems to display 23:59:59. is this a formatting issue? or a limitation of the formula?
0 -
From what I've found Domo's BeastMode is broken and limiting the delta to what you're seeing. This is not the indended output of the function as per mySQL documentation. I submitted a ticket quite some time ago citing the issue. I had to calculate instead in a mySQL dataflow. Good luck!
1 -
I'm having the same issue. I tried combining DATEDIFF and TIMEDIFF like:
DATEDIFF(col1,col2)*24*60 + TIME_TO_SEC(TIMEDIFF(col1,col2))*60
but it still isn't working with dates that are less than 24 hours difference, but fall on different calendar days. i.e 10/09/2017 23:00:00 and 10/10/2017 03:00:00. This should give an answer of 240 minutes, but instead I'm getting 1,680 minutes.
I think I'll have to do these calculations on the backend because Domo just doesn't seem to have the ability to easily do a timestamp difference, but someone please correct me if I'm wrong.
0 -
Try this. It works for me
NULLIF(DATEDIFF(`metric_set_initially_assigned_at`,`metric_set_created_at`),0)*24*60+NULLIF(HOUR(`metric_set_initially_assigned_at`-`metric_set_created_at`) ,0)*60
0 -
This code should work for difference between timestamp longer than a day.
If you had Start Date of 19-Sep-18 2:31:25 and End Date of 20-Sep-18 3:15:22. The time differnce in minutes should be 1483.57( 57 is the seconds).
((DATEDIFF(`end date`,`start date`)*1440)+(HOUR(TIMEDIFF(`end date`,`start date`))*60)+(MINUTE(TIMEDIFF(`end date`,`start date`)))+((SECOND(TIMEDIFF(`end date`,`start date`)))/100))
0
Categories
- All Categories
- 1.2K Product Ideas
- 1.2K Ideas Exchange
- 1.3K Connect
- 1.1K Connectors
- 273 Workbench
- 1 Cloud Amplifier
- 3 Federated
- 2.7K Transform
- 78 SQL DataFlows
- 525 Datasets
- 2.1K Magic ETL
- 2.9K Visualize
- 2.2K Charting
- 434 Beast Mode
- 22 Variables
- 510 Automate
- 114 Apps
- 388 APIs & Domo Developer
- 8 Workflows
- 26 Predict
- 10 Jupyter Workspaces
- 16 R & Python Tiles
- 332 Distribute
- 77 Domo Everywhere
- 255 Scheduled Reports
- 66 Manage
- 66 Governance & Security
- 1 Product Release Questions
- Community Forums
- 40 Getting Started
- 26 Community Member Introductions
- 68 Community Announcements
- 4.8K Archive