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

Best Answers

  • AS
    AS 🔵
    Accepted Answer

    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

  • user02957
    Accepted Answer

    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

     

     

Answers

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

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

  • 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.

  • This worked, thanks.

  • 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
    AS 🔵

    @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?

  • 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?

  • 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!

  • 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.

  • 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))