Beastmode - Duration (mins) between 2 Datetime Columns

Reply
Highlighted
Visitor

Beastmode - Duration (mins) between 2 Datetime Columns

If I have 2 datetime columns (StartDate & EndDate), how do I find the duration (mins) between the 2 using Beastmode?

 

Example: 

DateTime.JPG


Accepted Solutions
AS Black Belt
Black Belt

Re: Beastmode - Duration (mins) between 2 Datetime Columns

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"
Visitor

Re: Beastmode - Duration (mins) between 2 Datetime Columns

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

 

 


All Replies
Yellow Belt

Re: Beastmode - Duration (mins) between 2 Datetime Columns

Have you tried TIMESTAMPDIFF(MINUTE, Col1, Col2)? That works in MySQL, not sure if it will in BeastMode though.

Yellow Belt

Re: Beastmode - Duration (mins) between 2 Datetime Columns

Or have you tried TIMESTAMP(Col2) - TIMESTAMP(Col1)? (Although some conversion may be necessary to get it into "minutes"...)

AS Black Belt
Black Belt

Re: Beastmode - Duration (mins) between 2 Datetime Columns

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"
Visitor

Re: Beastmode - Duration (mins) between 2 Datetime Columns

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.

Visitor

Re: Beastmode - Duration (mins) between 2 Datetime Columns

This worked, thanks.

White Belt

Re: Beastmode - Duration (mins) between 2 Datetime Columns

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.

AS Black Belt
Black Belt

Re: Beastmode - Duration (mins) between 2 Datetime Columns

@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:MMSmiley FrustratedS, 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"
Visitor

Re: Beastmode - Duration (mins) between 2 Datetime Columns

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?

Yellow Belt

Re: Beastmode - Duration (mins) between 2 Datetime Columns

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!

Announcements
Looking for the latest Community solutions? Please visit our accepted solutions board here!