Can duration dimension group show a mix of intervals?

Looker Q&A Looker
1

I have a duration dimension. I want it to show the number of days and hours. For example, if the difference between sql_start and sql_end is 60.4 hours, I want it to show 2 days, 12 hours. Instead it either shows 3 days or 60 hours.

I know how to do this manually, but I find it surprising that the duration function doesn’t have this natively. I couldn’t find anywhere in https://cloud.google.com/looker/docs/reference/param-field-dimension-group on how to do this, and Gemini suggested writing several helper functions.

Is there an easy way to do this? I feel like this kind of defeats the purpose of the duration functionality for most visualization purposes.

1 Like
2

Hi Josh,

the trick is to first calculate the difference in decimal days and then use value_format to convert it to the desired format.

view: test {
derived_table: {
  sql: SELECT '20250312 20:05:00' startdate, '20250319 07:20:00' enddate;;
}

  dimension: days {
    sql: DATEDIFF(second, startdate,  enddate)/60/60/24.00;;
    value_format_name: decimal_1
  }

  dimension: days_hours {
    sql: ${days} ;;
    type: number
    value_format: "d\" days, \"hh\" hours\""
  }
}

Screenshot 2025-03-19 at 20.59.59.png
Screenshot 2025-03-19 at 20.59.59.png356×100 3.17 KB

Let me walk you through the two dimensions.

days

It is important DATEDIFF(second, […])/60/60/24.00) as using DATEDIFF(day, […]) would only give you a whole number. Alternatively you could simplify it to DATEDIFF(second, […])/86400.00). The two decimal zeros ensure that the output will be a decimal number, too.

days_hours

Next, use the dimensions days as input for the days_hours dimension. Subparameter type has to be number. You then set the value_format subparameter to the output you would like to have following the guidelines for customizing a number format as defined by Microsoft.

In your case you wanted to show the number of days and the number of hours and two strings, remember to escape the quotes of the string using a backslash.

Hope this helps!

3

Good point! I think you’re right that all of the built in durations will round. I think you’d have to write a custom dimension to get a day-hour display.

4

Thanks. This is what I had. I was hoping to find something that still used the “duration” data type. It seems that Looker does not handle durations well, and I should just use numbers in general.

5

The type of my solution will still be “number” so it still sorts correctly and filters can be set like a number as well.

6

Could we have a feature to control the rounding on the dimension_group that uses type duration?