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 toyes
. 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 toyes
orno
?
-- 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)
- What's the current time, as the Looker user? For example, in Snowflake, run
- 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.
This content is subject to limited support.