How Looker does timezones and how to troubleshoot them (Community)

View the original community article here

Last tested: Feb 1, 2021
 

Timezones, yikes!

You may need to troubleshoot timezones to answer questions like:

• Why isn't Looker converting timezones correctly?
• Why is the timezone CONVERT SQL wrong? Inaccurate?
• Is there a timezone conversion bug?
• My finance team says timezone conversions are incorrect because order X is missing from our Explore. Why is Looker not doing this correctly?

The goal is this article is to help you understand how Looker does timezones and how that interacts with your database.

How Looker does timezones

• Connections Panel, Database Timezone: This is the timezone Looker THINKS dates are stored in. Will show up as one of the parameters in the SQL Looker generates to convert_timezone in generated SQL, on most dialects.
• Connections Panel, Query Timezone: This is the timezone Looker will attempt to convert timestamps TO. Shows up as the other parameter in the SQL Looker generates to convert_timezone.
• User Setting: User Timezone; "User Specific Timezones" related. This will replace the query timezone in Admin> Connections. If the feature is enabled in Admin>Settings, then the following two things will occur:
  • (1) Users will have a timezone set in their user settings. This timezone will be the default timezone all queries run by that user will convert to
  • (2) Users will see a timezone selector on all content (Explores, Dashboards, Looks). They can override their user timezone by selecting a different timezone in this drop down. If they don't override via the drop down, they will use the default timezone. In other words, with this enabled users can use a different timezone for every individual piece of content, even individual tiles on a dashboard.
• Admin Settings, Application Timezone: Used for logging and other Looker-things. Becomes the default query timezone if user specific timezones are enabled and no timezone was set for that user.
• convert_tz parameter in LookML: This parameter enables/disables timezone conversions for every use of the specific field it is used on. By default, it's a hidden parameter set to yes . Setting to no disables all timezone conversion on that dimension/dimension_group, or parameter/filter_only_field

How databases do timezones

Typically databases handle timezones with the following:

• Database Timezone: The timezone the database stores datetime fields as. The Database Timezone is what Looker's 'Database Timezone' setting should match. Note: In Redshift, this will always be UTC.
• Session Timezone. When Looker initiates a connection to the DB, it will set this parameter as the Query Timezone (in connection settings) or the User's timezone (if In Snowflake, we can use the SHOW PARAMETERS command to see these parameters, and other dialects have similar mechanisms.

Troubleshooting Timezones

• Check the Run timezone
  -- Does this match what the user is expecting? Does it match the users physical location?
  
• Review the underlying Field or Dimension_Group
  -- Is there a convert_tz parameter? Is it set to yes or no?
  -- Does the option align with what the user expects?
  -- Check the datatype of the timestamp field. If it's a timestamp_tz, see this card: Troubleshooting Timestamp_tz. If it's a date, try adding datatype: date to the dimension group definition.
• Review the Timezone settings configured in Admin Panel
  -- Application Time Zone (Admin > General Settings)
  -- Database Time Zone (Admin > Database Connection)
  • What's the current time, as the Looker user? For example, in Snowflake, run CURRENT_TIMESTAMP to display this; compare to the same query run directly in the DB.
    -- Query Time Zone (Admin > Database Connection)
    -- If enabled, User Specific Time Zone (Admin > General Settings)
• Review any relation to PDTs or user's ETL
  -- If there is still an issue or discrepancy with the users data, then the issue may be tied to PDTs or the user's ETL and that can attribute to the 'staleness' of the data.
  -- For Example: an order may not show up for This Week because that order hasn't been included in the most recent ETL run.
  -- They could have just missed timed it by a couple of hours
• If Looker is applying timezone conversions, you will see timezone conversion applied in the generated SQL. If there is no timezone conversion in the generated SQL, then Looker is not doing any timezone conversion.