(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). '
``````

#2

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

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.