Usage
view: view_name {
dimension: field_name {
convert_tz: yes | no
}
}
|
Hierarchy
convert_tz |
Possible Field Types
Dimension, Dimension Group, Measure, Filter, Parameter
Accepts
A Boolean (yes or no)
|
Definition
Looker has various time zone settings that convert time-based data between different time zones. Looker does time zone conversion by default. If you do not want Looker to perform a time zone conversion for a particular dimension, dimension_group (with type: time), or filter field, then you can use the convert_tz parameter. This can be useful for fields that are already converted to the appropriate time zone, or in some advanced situations where you need to avoid a double time zone conversion.
In general, time computations (differences, durations, etc.) only work correctly when you operate on time values that are all converted to the same time zone. It is important to keep time zones in mind when writing LookML.
Examples
Do not perform time zone conversion for the local_created dimension group:
dimension_group: local_created {
type: time
timeframes: [time, date, week, month]
sql: ${TABLE}.local_created_at ;;
convert_tz: no
}
Things to consider
convert_tz: no applies only to a dimension, not to a filter that uses the dimension. In other words, filters always perform time zone conversion. When you specify convert_tz: no, time-based data values are displayed in the database time zone, but are filtered using the query time zone.
Because filters always do time zone conversion, a difference between the database time zone and query time zone could cause data to unexpectedly be included or excluded from a dataset. To avoid this, ensure that the query time zone is set to the same value as the database time zone.
If User Specific Time Zones is enabled, set the time zone drop-down menu (located next to the Run button on Explores, Looks, and dashboards) to the same value as the database time zone. If User Specific Time Zones is disabled, set the Query Time Zone to the same value as the database time zone.
If you're using custom filters, keep time zone conversion enabled to ensure valid date comparisons. If you turn off time zone conversion with convert_tz: no and include the field in a custom filter, your date comparisons may be invalid.
Database dialect support for time zone conversion
For Looker to convert time zones in your Looker project, your database dialect must support time zone conversion. The following table shows which dialects support time zone conversion in the latest release of Looker:
| Dialect | Supported? |
|---|---|
| Actian Avalanche | No |
| Amazon Athena | Yes |
| Amazon Aurora MySQL | Yes |
| Amazon Redshift | Yes |
| Apache Druid | No |
| Apache Druid 0.13+ | Yes |
| Apache Druid 0.18+ | Yes |
| Apache Hive 2.3+ | Yes |
| Apache Hive 3.1.2+ | Yes |
| Apache Spark 3+ | Yes |
| ClickHouse | No |
| Cloudera Impala 3.1+ | Yes |
| Cloudera Impala 3.1+ with Native Driver | Yes |
| Cloudera Impala with Native Driver | Yes |
| DataVirtuality | No |
| Databricks | Yes |
| Denodo 7 | No |
| Denodo 8 | No |
| Dremio | Yes |
| Dremio 11+ | Yes |
| Exasol | No |
| Firebolt | No |
| Google BigQuery Legacy SQL | No |
| Google BigQuery Standard SQL | Yes |
| Google Cloud PostgreSQL | Yes |
| Google Cloud SQL | Yes |
| Google Spanner | Yes |
| Greenplum | Yes |
| HyperSQL | No |
| IBM Netezza | Yes |
| MariaDB | Yes |
| Microsoft Azure PostgreSQL | Yes |
| Microsoft Azure SQL Database | Yes |
| Microsoft Azure Synapse Analytics | Yes |
| Microsoft SQL Server 2008+ | No |
| Microsoft SQL Server 2012+ | No |
| Microsoft SQL Server 2016 | Yes |
| Microsoft SQL Server 2017+ | Yes |
| MongoBI | No |
| MySQL | Yes |
| MySQL 8.0.12+ | Yes |
| Oracle | Yes |
| Oracle ADWC | Yes |
| PostgreSQL 9.5+ | Yes |
| PostgreSQL pre-9.5 | Yes |
| PrestoDB | Yes |
| PrestoSQL | Yes |
| SAP HANA 2+ | No |
| SingleStore | Yes |
| SingleStore 7+ | Yes |
| Snowflake | Yes |
| Teradata | No |
| Trino | Yes |
| Vector | No |
| Vertica | Yes |