Readable times from seconds

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). '
1 11 6,751
11 REPLIES 11

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.

@tagga,

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!

@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.

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})

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

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

Hi,

Be careful. If the time_hours measure is greater than 24 hours, you will get an incorrect reading with “h:mm:ss”, because that format omits full days. Better use “[hh]:mm:ss”.

willbaker
Participant I

Hello, I am currently utilising this value format “[hh]:mm:ss”, however it appears that it cannot handle negative time values? Am I doing something incorrectly, my setup is something like this:

measure: wait_time_avg {
type: average
value_format: “[hh]:mm:ss;([hh]:mm:ss)”
sql: ${wait_time} ;;
}

Any advice on how to properly filter such dimensions / measures?

I think it’s a lot easier to get the time as a fraction of the day and just format the value:

dimension:my_time_difference
type:number
sql:datediff(sec, myfirst_time, second_time)/(60.0*60*24)
value_format: "HH:MM:SS"

I’ll note that that the last I checked, this value_format is not documented. However, it works just fine.

Hello, I am currently utilising this value format “[hh]??ss”, however it appears that it cannot handle negative time values? Am I doing something incorrectly, my setup is something like this:

measure: wait_time_avg {

type: average

value_format: “[hh]??ss;([hh]??ss)”

sql: ${wait_time} ;;

}

@willbaker why are you getting negative wait times? I would recommend doing some data cleaning on your raw data or writing in the CASE WHEN statement in your SQL to only calculate wait time for customers when end time of waiting is >= the beginning time stamp of the wait time.

Top Labels in this Space
Top Solution Authors