convert_tz

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