Date Calculations - minutes and seconds

Reply
Highlighted
Yellow Belt

Date Calculations - minutes and seconds

Hi, Experts,

 

I am working on trying to find out how to calculate the time difference between dates.

 

dates.png

Here is what I am after:

 

I would like to see the difference lets pick two dates start (created_date) - end (posted_date). I would like the output to show the time in minutes:seconds or minutes.seconds of the lapse between the time. If you also apply that same formula that will be generated to another set of dates, lets say start (completed_date) - end (promised_time) will the output be more or less the same or will the calculation need to be different for the latter?

 

I appreciate your help in advance.

 

-Isaiah


_
Isaiah Melendez
The most efficient way to optimize a SQL query is to eliminate it.

Accepted Solutions
Highlighted
Brown Belt

Re: Date Calculations - minutes and seconds

@jaeW_at_Onyx - Yes. UNIX_TIMESTAMP will convert the date to the number of seconds since 1970-01-01 00:00:00 so subtracting those two together will get you the total elapsed seconds between two date times.

 

If you're wanting to display it as MInutes : Seconds you'd need to do some additional math.

 

The following beast modes return the date parts in that elapsed time. They're utilizing CURRENT_TIMESTAMP and a `Date` field to show elapsed time from now. If you want to use two different fields replace CURRENT_TIMESTAMP with your END timestamp and `Date` with your START timestamp.

 

Days:

 

ROU D((UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`) - MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 86400)) / 86400)

Hours:

 

 

ROUND((MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 86400) - MOD(MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 86400), 3600)) / 3600)

Minutes:

 

 

ROUND((MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 3600) - MOD(MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 3600), 60)) / 60)

Seconds:

 

 

ROUND(MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 60) - 0.5) -- Whole elapsed seconds

Finally, bringing it all together:

 

CONCAT(
  ROUND((UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`) - MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 86400)) / 86400), 'd ',
  ROUND((MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 86400) - MOD(MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 86400), 3600)) / 3600), ':',
  ROUND((MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 3600) - MOD(MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 3600), 60)) / 60), ':',
  ROUND(MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 60) - 0.5)
)

 

 

CC @imelendez 

 

If you don't want to break out hours and days and only use minutes and seconds:

CONCAT(
  ROUND(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`) / 60 - 0.5), ':',
  ROUND(MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 60) - 0.5)
)

 



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post


All Replies
Highlighted
Major Red Belt

Re: Date Calculations - minutes and seconds

@imelendez 

I'm not sure I understand your question.

If i had to guess though you're trying to calculate duration.  If so,wrap your dates in the UNIX_TIMESTAMP and then take the difference.

 

duration_in_seconds = UNIX_TIMESTAMP(end_dateTime) - UNIX_TIMESTAMP(begin_dateTime)

 

@GrantSmith , I think that's the solution you found that works right?

 


Jae Wilson
Check out my Domo Training YouTube Channel

**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"
Highlighted
Brown Belt

Re: Date Calculations - minutes and seconds

@jaeW_at_Onyx - Yes. UNIX_TIMESTAMP will convert the date to the number of seconds since 1970-01-01 00:00:00 so subtracting those two together will get you the total elapsed seconds between two date times.

 

If you're wanting to display it as MInutes : Seconds you'd need to do some additional math.

 

The following beast modes return the date parts in that elapsed time. They're utilizing CURRENT_TIMESTAMP and a `Date` field to show elapsed time from now. If you want to use two different fields replace CURRENT_TIMESTAMP with your END timestamp and `Date` with your START timestamp.

 

Days:

 

ROU D((UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`) - MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 86400)) / 86400)

Hours:

 

 

ROUND((MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 86400) - MOD(MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 86400), 3600)) / 3600)

Minutes:

 

 

ROUND((MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 3600) - MOD(MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 3600), 60)) / 60)

Seconds:

 

 

ROUND(MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 60) - 0.5) -- Whole elapsed seconds

Finally, bringing it all together:

 

CONCAT(
  ROUND((UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`) - MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 86400)) / 86400), 'd ',
  ROUND((MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 86400) - MOD(MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 86400), 3600)) / 3600), ':',
  ROUND((MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 3600) - MOD(MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 3600), 60)) / 60), ':',
  ROUND(MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 60) - 0.5)
)

 

 

CC @imelendez 

 

If you don't want to break out hours and days and only use minutes and seconds:

CONCAT(
  ROUND(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`) / 60 - 0.5), ':',
  ROUND(MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`Date`), 60) - 0.5)
)

 



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post

Highlighted
Yellow Belt

Re: Date Calculations - minutes and seconds

Wow this is great feedback gentlemen @GrantSmith and @jaeW_at_Onyx . I appreciate the wisdom.

 

I apologize for @jaeW_at_Onyx if I wasn't as clear as I should have been. Let me elaborate.

 

So we are currently doing this original calculation but it does not seem to be working for one of metrics (when doing the duration math between promised time and completed date):

SUM(((DATEDIFF(`promised_time`,`completed_date`)*1440)+(HOUR(TIMEDIFF(`promised_time`,`completed_date`))*60)+(MINUTE(TIMEDIFF(`promised_time`,`completed_date`)))+((SECOND(TIMEDIFF(`promised_time`,`completed_date`)))/100))) / NULLIF(COUNT(distinct `ro_no`),0)

@GrantSmith, I implemented your solution with just minutes and seconds and I got som huge number.

 

dates_v1.png

 Here is your implemented code:

CONCAT(
  ROUND(UNIX_TIMESTAMP(`completed_date`) - UNIX_TIMESTAMP(`promised_time`) / 60 - 0.5), ':',
  ROUND(MOD(UNIX_TIMESTAMP(`completed_date`) - UNIX_TIMESTAMP(`promised_time`), 60) - 0.5)
)

Thanks again for helping me figure this out, guys!


_
Isaiah Melendez
The most efficient way to optimize a SQL query is to eliminate it.
Highlighted
Major Red Belt

Re: Date Calculations - minutes and seconds

@imelendez  check your parenthesis and break it all into composite parts.

 

 

CONCAT(
  ROUND(UNIX_TIMESTAMP(`completed_date`) - UNIX_TIMESTAMP(`promised_time`) / 60 - 0.5), ':',
  ROUND(MOD(UNIX_TIMESTAMP(`completed_date`) - UNIX_TIMESTAMP(`promised_time`), 60) - 0.5)
)

 

UNIX_Timestamp converts to seconds since XYZ point in time.

So ignoring the ROUND.  

(

      UNIX_TIMESTAMP(`completed_date`) - UNIX_TIMESTAMP(`promised_time`)

) / 60

 

Should give you the difference between the two dates in seconds then divided by sixty should be the difference in minutes.  Does it pass the sniff test?

 

If so, then it's just a matter of formatting it into Hours or Days or Months. which is all the ROUND and the CONCAT that @GrantSmith  added.


Jae Wilson
Check out my Domo Training YouTube Channel

**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"
Announcements
Stand out in the Dojo: Choose your own recognizable username and add a unique profile photo in your profile settings.