Magic ETL

Magic ETL

Domo IDEAs Conference - Beast Modes - Time Difference Formatting

Greetings! This is another post highlighting a beast mode from my Domo IDEAs conference session. This one covers how to format a different between two date time values.

Problem:

How can I display the difference between two timestamps values in a human readable format?

Solution:

We can utilize the UNIX_TIMESTAMP function to calculate the number of seconds which have elapsed since January 1st 1970 12:00AM. By subtracting these these two values it will give us the number of seconds between the two timestamps. We then can do some simple division to determine the number of days, hours, minutes and seconds.

Days:

  1. -- Description:
  2. -- UNIX_TIMESTAMP returns the number of seconds since 1970-01-01
  3. -- UNIX_TIMESTAMP - UNIX_TIMESTAMP returns the number of seconds between the right now and the value in `dt`
  4. -- Then divide by 24*60*60 = 86400 (number of seconds in a day)
  5. -- Floor returns the whole number of days that have elapsed
  6. FLOOR((UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`dt`)) / (24*60*60))
  7.  

Hours:

  1. -- Description:
  2. -- Determine the number of hours ago (partial days)
  3. FLOOR(
  4.   -- After taking out whole days get the remainder of seconds. 86400 seconds = 1 day
  5.   (MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`dt`), 86400))
  6.   -- Finally divide by 60*60 = 3600 to convert to hour units
  7.   / 3600)
  8.  

Minutes:

  1. -- Description:
  2. -- Determine the number of remaining minutes to go in our timespan (since now)
  3.  
  4. -- Divide by whole hours (3600 seconds) and get the remainer seconds using MOD
  5. FLOOR((MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`dt`), 3600))
  6.   -- Finally convert to minute units
  7.   / 60)
  8.  

Seconds:

  1. -- Description:
  2. -- Simply divie by 60 and get the remaining seconds using Modulo operation (MOD function).
  3. FLOOR(MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`dt`), 60)) -- Whole elapsed seconds
  4.  


Finally we can combine all of the beast modes together to make a single string containing the formatted difference:

  1. -- Author:
  2. -- Created:
  3. -- Last Modified:
  4. -- Description:
  5. -- UNIX_TIMESTAMP returns the number of seconds since 1970-01-01
  6. -- Using some simple math we can calculate the time since a specific date.
  7. -- For a more detailed breakdown of what each step is doing refer to Days Ago, Hours Ago, Minutes Ago or Seconds Ago
  8. -- This concatenates all of those values together in a readable format.
  9.  
  10. CONCAT(
  11.   -- Simplify formatting of negative time differences by conditionally adding a negative
  12.   -- and then subsequently taking the absolute values.
  13.   -- This is to avoid getting times like -1d -12:-9:-11
  14.   -- and instead format like -1d 12:9:11
  15.   CASE WHEN CURRENT_TIMESTAMP() <= `dt` THEN '-' ELSE '' END,
  16.   -- Days
  17.   ABS(FLOOR((UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`dt`))
  18.   -- - MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`dt`), 86400))
  19.   / 86400)),
  20.   'd ',
  21.   -- Hours
  22. ABS(FLOOR(
  23.   (MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`dt`), 86400))
  24. -- - MOD(MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`dt`), 86400), 3600))
  25.   / 3600)),
  26.   ':',
  27.   -- Minutes
  28. ABS(FLOOR((MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`dt`), 3600))
  29. -- - MOD(MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`dt`), 3600), 60))
  30.   / 60)),
  31.   ':',
  32.   -- Seconds
  33. ABS(FLOOR(MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`dt`), 60)))
  34.  
  35.   )
  36.  
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In

Comments

  • Hi @GrantSmith ,

    I've just watched your Domo Ideas Exchange session. Thanks for the great work! :D

    I noticed your Beast Mode was returning incorrect values for future dates, and while I was at it I also simplified the code to make it more readable (at least for me). Here's my proposal:


    CONCAT(

    CASE WHEN CURRENT_TIMESTAMP() <= `dt` THEN '-' ELSE '' END,

    -- Days

    FLOOR ( ABS ( ( UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`dt`) ) ) / 86400 ),

    'd ',

    -- Hours, Minutes and Seconds

    SEC_TO_TIME ( ABS ( MOD ( UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`dt`), 86400 ) ) )

    )



    I didn't update the Beast Mode in the Dojo instance, though. Please let me know if you want me to add this Beast Mode as an alternative approach in the original DataSet :)



    Best,

    Amedeo

  • @AmedeoM

    You're indeed correct there was an issue with future dates. Your version corrects this issue and I've updated the beast mode on the dojo instance. Thanks for your contribution!

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Here's a link to the video of my session outlining this beast mode: https://www.youtube.com/watch?v=gO8OLpsAk4M&amp;index=6

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In