Last tested: Feb 1, 2021
You may need to troubleshoot timezones to answer questions like:
The goal is this article is to help you understand how Looker does timezones and how that interacts with your database.
query timezone
in Admin> Connections. If the feature is enabled in Admin>Settings, then the following two things will occur: 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_fieldTypically databases handle timezones with the following:
SHOW PARAMETERS
command to see these parameters, and other dialects have similar mechanisms.yes
or no
?CURRENT_TIMESTAMP
to display this; compare to the same query run directly in the DB.This content is subject to limited support.
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:
The actual database timezone can be updated by Looker upon connection.
This doesn’t happen with all dialects
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.
This impacts performance
Snowflake
SELECT current_session()
ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = 'TIMESTAMP_LTZ'
This adds 1.8s in this anecdote
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
ideally its use is avoided, since it leads to confusion and some odd behavior from the analyst’s perspective
Can only be set at field level (though I think in older versions the validator would not create an error about this)
Datatype: date in a date dimension/dimension_group also prevents tz conversion in the same way convert_tz: no does
Content-level timezones
When saving, a user can set timezone on a look or tile (also dashboard? Need to test)
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
When viewing, a user can set timezone on a dashboard or look
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
...I think this realm has evolved, this info might be outdated
Native Derived Tables:
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.
Important feature is the timezone: “string” NDT parameter. docs (check out “timezone” in the property list)
if this is not set, the default is no conversion
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
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:
Cast as date in column: reference (e.g. column: event_date)
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.
Good post, one question regarding to “
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