How Looker does timezones and how to troubleshoot them

[jamesnestler]

Knowledge Drop

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.

This content is subject to limited support.                

[andy4]

I love this post! I made a similar document and have been sharing it with my customers for years. I think it covers similar content as this article, but wanted to post it all the same.

Table below contains the most critical time zone summary.  Below that are more specialized cases.

Date with timezone	Controlled by	If that isn’t set	If convert_tz: no
Schedule send at	Explicitly set on the scheduler modal (default is user specific tz of the saving user)	Not applicable (default will be Application tz if user-specific tz isn’t enabled)	Not applicable
User-input filter value “from” tz, absolute and relative dates	Database tz in connection settings	No timezone conversion possible!	No effect
User-input filter value “to” tz, absolute and relative dates	User specific tz	Query tz in connection settings	No effect
Actual field being filtered “from” tz	Database tz in connection settings	No timezone conversion possible!	Not applicable
Actual field being filtered “to” tz	User specific tz	Query tz in connection settings	Use select clause “from” tz
Select clause “from” tz	Database tz in connection settings	No timezone conversion possible!	Not applicable
Select clause “to” tz	User specific tz	Query tz in connection settings	Use select clause “from” tz
Database time functions e.g. NOW() used in dimension or measure sql	Database server	Not applicable	Not applicable
Database time functions used in datagroup sql_trigger	Database server	Not applicable	Not applicable
Database time functions used in pdt sql_trigger_value	Database server	Not applicable	Not applicable
PDT cron	Application tz	Not applicable	Not applicable

Looker can modify database system timezone:

1. The actual database timezone can be updated by Looker upon connection.

2. This doesn’t happen with all dialects

3. This causes extra query(ies) to be run before the actual query to change the dbase system clock to the one specified in the connection settings. 

4. This impacts performance 

   1. Snowflake

      1. SELECT current_session()

      2. ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = 'TIMESTAMP_LTZ'

      3. This adds 1.8s in this anecdote 

5. ​​​​​​​Best practice would be to match your database timezone to the one Looker believes it is set in, in the connection setting page.

Convert_tz

1. ideally its use is avoided, since it leads to confusion and some odd behavior from the analyst’s perspective

2. Can only be set at field level (though I think in older versions the validator would not create an error about this)

3. Datatype: date in a date dimension/dimension_group also prevents tz conversion in the same way convert_tz: no does

Content-level timezones

1. When saving, a user can set timezone on a look or tile (also dashboard? Need to test)

2. The default when saving is the saving user’s timezone, but they can choose to save it to the flexible “user timezone” which will make the look/tile reflect the viewer’s timezone, or they can explicitly save it to any other timezone

3. When viewing, a user can set timezone on a dashboard or look

4. On a dashboard… If some tiles on a dashboard are saved as Pacific time, and others are saved as user-specific, even if the user is in Pacific time, the dashboard will say the timezones are “each tile’s timezone” - need to test this

5. ...I think this realm has evolved, this info might be outdated

Native Derived Tables:

1. Problem is that it is possible to convert timezone when calling a field as a NDT column, then again when it is used as a dimension.

2. Important feature is the timezone: “string” NDT parameter. docs (check out “timezone” in the property list)

   1. if this is not set, the default is no conversion

   2. For non-persisting NDTs, an option is to set the timezone: “string” as “user timezone” and then it will detect the viewing user’s tz and do that

3. If timezone is converted in the explore_source, then it is likely it should not be converted again in the dimension.  This is the process:

   1. Cast as date in column: reference (e.g. column: event_date)

   2. Use timezone: “string of tz” in explore_source

Use datatype: date in the dimension_group (Datatype: date prevents tz conversion)

Also related, and apparently undocumented, is the ability to cast dates as timestamps and strings right in the substitution operator. This is handy in joins or when using a date in an NDT. The syntax is ${my_date_field::date}
${my_date_field::timestamp}
${my_date_field::string}
Of course you can also do this casting with raw sql, but this is nifty and lets Looker write the sql.

[skywalker115959]

Good post, one question regarding to “

• 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.”

In our database which is located in US, the dates are stored in UTC format.
If the time zone for the server location is Pacific Standard Time, please advise which time zone, we should set for “Database Timezone”,  UTC or Pacific Standard Time?

thanks