Beastmode - Duration (mins) between 2 Datetime Columns

Reply
Welcome to Dojo

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

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.

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

 

 

Welcome to Dojo

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

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

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