Using Looker Parameters for Dynamic Timezones


#1

Earlier this year, the Sydney-based organisation I work for launched in the UK. While this was super exciting for the company, it came with a whole lot of new data modelling challenges, one of which was handling timezones.

Prior to this launch, we stored everything in UTC and set the Query Timezone to 'Australia/Sydney' – sure it meant we were a little off for our activity in Perth, but for the most part it was working well.

As we worked on a solution to handle our global expansion we realised that different Looker users need to see different timezones:

  • Our Product and Marketing teams are interested in seeing times based on the app user’s timezone so they can understand user behaviour. (This covers most of our Looker users)
  • Our Finance team are interested in seeing everything reported in 'Australia/Sydney' time since this is our accounting calendar.
  • Our engineers are interested in seeing times in UTC since it makes it easier to reconcile timestamps with the app database.

I implemented a solution that allows each Looker user to select which timezone they would like to report in, using the following approach.

1. Create a view called parameters with a reporting_timezone parameter
In order for our Looker users to be able to select their timezone, I added a parameter called reporting_timezone. The default value is the most commonly used timezone – the local timezone.

# parameters.view.lkml

view: parameters {
  parameter: reporting_timezone {
    default_value: "Local"
    allowed_value: {
      value: "Local"
    }
    allowed_value: {
      value: "Australia/Sydney"
    }
    allowed_value: {
      value: "Europe/London"
    }
    allowed_value: {
      value: "UTC"
    }
  }
}

This parameter exists in a separate view as I wanted to share this across a number of explores.
I also have a number of other parameters shared across explores in our parameters view, for example, which currency a user wants to report in.

2. Join the parameters view to your explores
To access the new reporting_timezone parameter, join the view to your explores, like so:

explore: users {
  join: parameters {}
}

3. For each view, build a local_timezone and reporting_timezone field
I model our data before it gets to Looker using dbt. Part of this modelling process means that for each table, I have a field with a timezone string (e.g. 'Australia/Sydney') representing the local timezone.
In Looker, I surfaced this field as the dimension local_timezone.
I then created a reporting_timezone dimension in each view – this dimension uses Liquid to evaluate the reporting_timezone parameter, and returns the correct timezone to use.

view: users {
  ...

  dimension: local_timezone {
    type: string
    sql: ${TABLE}.local_timezone ;;
  }

  dimension: reporting_timezone {
    type: string
    sql: {% if parameters.reporting_timezone._parameter_value == "'Local'" %}
    ${local_timezone}
    {% else %}
    {{parameters.reporting_timezone._parameter_value}}
    {% endif %};;
    can_filter: no
  }
  ...
}

Note the inclusion of double and single quotes ("' ... '") in the Liquid statement, and can_filter: no.

4. Adjust your time fields to use the reporting_timezone dimension

view: users {
  ...
  dimension_group: registered {
  type: time
  timeframes: [
    time,
    date,
    week,
    month,
    year
  ]
  sql: CONVERT_TIMEZONE(${reporting_timezone}, ${TABLE}.registered_at) ;;
  convert_tz: no
  }
  ...
}

Note the inclusion of convert_tz: no.
If a user selects Local as their timezone, the conversion is based on the local_timezone field, like so:

CONVERT_TIMEZONE(users.local_timezone, users.registered_at)

Whereas if the user chooses one of the pre-defined timezones, it uses the string instead:

CONVERT_TIMEZONE('Australia/Sydney', users.registered_at)

5. Voilà!
That’s it!
Now your Looker users can choose which timezone they want their data in.
It does mean a little bit of extra lookml in your views, and every so often we see reports where the numbers don’t reconcile because of differing timezones, but on the whole the pattern has worked really nicely for us.
One important thing I found was educating Looker users that the local timezone was based on the app user, not on the Looker user – I’d welcome any feedback for a better label :slight_smile:


Dashboard - change date type in elements with a filter
(Izzy) #2

Nice pattern, Claire! Our cofounder @lloydtabb always says “hell hath a timezone”, but looks like you’ve managed to make yours a little more comfortable. :slight_smile:

We’re always on the lookout for neat things like this from customers to help us understand what we should bake into the product itself, thanks for sharing!

Anyone else out there have any cool timezone tricks?

ps: end_user_timezone? One more word, I know, but…


#3

Ooh I like end_user_timezone. An extra word for the sake of clarity is worth it :slight_smile: