Readable times from seconds

(marcell) #1

It’s often times useful to get a neatly displayed readable times. Typically this isn’t a problem, but if we want to know how many days/hours/minutes/seconds elapsed between two different times, this can be a bit tedious, since SQL only gives us one of those at a time (example in Redshift):

dimension: my_time_difference
type: number
sql: datediff(sec, my_first_time, my_second_time)

Whether we got this number of seconds from a difference as show above, or any other way, we can simply build the readable time using rounding and modular arithmetic:

dimension: my_readable_time_difference
sql: |
  floor(${my_time_difference}/(60*60*24))::VARCHAR || ' day(s) '
  || floor(MOD(${my_time_difference},(60*60*24))/(60*60))::VARCHAR || ' hour(s) '
  || floor(MOD(${my_time_difference},(60*60))/(60))::VARCHAR || ' minute(s) and '
  || MOD(${my_time_difference},(60))::VARCHAR || ' second(s). '


What if we’re dealing with a measure? This technique will not work.

In my opinion this should be handled with a format the way Excel does.

(Dirty Looker) #3


We do have a non modeling technique was well with our table calculations which was precisely designed to be familiar for folks who use Excel:

Hope this works for you!

(marcell) #4

@tagga, while the method @mikhailxu proposed is the preferred method, this can work with a measure as well if we want to build it into the model. The measure will simply have to be of type string.

(Alex K) #5

This is a LOT easier with format_value

- measure:  time_hours
  type: number
  sql: ${time_seconds} / 86400.0
  value_format: "h:mm:ss"

Your value needs to be in number of days (hence the divide by 86400.0 to get from seconds to days)

you can even use this in a sum measure too.

- measure:  time_hours_total
  type: sum
  sql:  ${time_hours}
  value_format: "h:mm:ss"

If you have a string like "2:34:57" in MySQL you can convert this to a time number with TIME_TO_SEC(${time_string})

(Sukhbat Lkhagvadorj) #6

Note that you should divide it by 86400.0 otherwise you get 0:00:00.

(Theo Geer) #7

Thank you so much. This is exactly what I needed to find today.