Beastmode - Duration (mins) between 2 Datetime Columns

Reply
White Belt

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.

White Belt

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

 

 

View solution in original post

White Belt

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
Check out these great solution videos! The new iSolveditWithDomoboard is live, share yours and earn higher rankings in Dojo click here!