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:

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

Hours:

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

Minutes:

-- Description:
-- Determine the number of remaining minutes to go in our timespan (since now)

-- Divide by whole hours (3600 seconds) and get the remainer seconds using MOD
FLOOR((MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`dt`), 3600))
  -- Finally convert to minute units
  / 60)

Seconds:

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

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

-- Author:
-- Created:
-- Last Modified:
-- Description:
-- UNIX_TIMESTAMP returns the number of seconds since 1970-01-01
-- Using some simple math we can calculate the time since a specific date.
-- For a more detailed breakdown of what each step is doing refer to Days Ago, Hours Ago, Minutes Ago or Seconds Ago
-- This concatenates all of those values together in a readable format.

CONCAT(
  -- Simplify formatting of negative time differences by conditionally adding a negative
  -- and then subsequently taking the absolute values.
  -- This is to avoid getting times like -1d -12:-9:-11
  -- and instead format like -1d 12:9:11
  CASE WHEN CURRENT_TIMESTAMP() <= `dt` THEN '-' ELSE '' END,
  -- Days
  ABS(FLOOR((UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`dt`))
  -- - MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`dt`), 86400))
  / 86400)),
  'd ',
  -- Hours
ABS(FLOOR(
  (MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`dt`), 86400))
-- - MOD(MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`dt`), 86400), 3600))
  / 3600)),
  ':',
  -- Minutes
ABS(FLOOR((MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`dt`), 3600))
-- - MOD(MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`dt`), 3600), 60))
  / 60)),
  ':',
  -- Seconds
ABS(FLOOR(MOD(UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(`dt`), 60)))
 
  )