Readable times from seconds

[marcell1]

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