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 -
You must use this formula for properly calculation, It will return the number of hours between ClosedDate and SubmitedDate and it works for short and long durations.
CASE
WHEN (DATEDIFF(ClosedDate
,SubmitDate
)*24)=0 THEN (TIME_TO_SEC(TIMEDIFF(ClosedDate
,SubmitDate
))/60/60)
WHEN HOUR(SubmitDate
) > HOUR(ClosedDate
) THEN (DATEDIFF(ClosedDate
-1,SubmitDate
)*24)+(TIME_TO_SEC(TIMEDIFF(ClosedDate
,SubmitDate
))/60/60)
WHEN HOUR(SubmitDate
) < HOUR(ClosedDate
) THEN (DATEDIFF(ClosedDate
,SubmitDate
)*24)+(TIME_TO_SEC(TIMEDIFF(ClosedDate
,SubmitDate
))/60/60)
WHEN HOUR(SubmitDate
) = HOUR(ClosedDate
) THEN
CASE
WHEN MINUTE(SubmitDate
) > MINUTE(ClosedDate
) THEN (DATEDIFF(ClosedDate
-1,SubmitDate
)*24)+(TIME_TO_SEC(TIMEDIFF(ClosedDate
,SubmitDate
))/60/60)
WHEN MINUTE(SubmitDate
) < MINUTE(ClosedDate
) THEN (DATEDIFF(ClosedDate
,SubmitDate
)*24)+(TIME_TO_SEC(TIMEDIFF(ClosedDate
,SubmitDate
))/60/60)
END
END0 -
Probably not the most efficient way to go about it but a lot of times when I need time differences in hours or minutes, I just do this:
datediff(end date, start date) * 1440 (minutes in a day)
+
hour(end date) - hour(start date) * 60
+
minute(end date) - minute(start date)0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 102 SQL DataFlows
- 626 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 755 Beast Mode
- 61 App Studio
- 41 Variables
- 693 Automate
- 178 Apps
- 456 APIs & Domo Developer
- 49 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 398 Distribute
- 115 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 11 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 110 Community Announcements
- 4.8K Archive