Timezone Conversion

Timezones can be set in Looker on the database connection to globally ensure all type: time dimensions will be converted from a specified timezone into another specified timezone.

The image below shows the connection page, with the database and query timezones pointed out. This screen can be reached by first going to the Admin section, then choosing the Connections tab.

This shows the SQL generated by the above selections:

This conversion can also be turned off on a dimension level using the convert_tz parameter:

- dimension_group: created
  type: time
  timeframes: [time, date]
  convert_tz: false

Timezone conversion can also be manually defined within the sql parameter of a dimension as well:

- dimension: created
  type: time
  timeframes: [time, date]
  sql: CONVERT_TZ(${TABLE}.created_at,'UTC','PST')

##Database-Specific Quirks
There are some database-specific factors, discussed in the sections below.

MySQL

MySQL requires a timezone table before the conversion function works. This can be run by an admin. Read more here.

Postgres

Looker uses the driver setting to select the target timezone. This may affect how queries are processed in SQL Runner, versus your PG Admin, because Looker will use the current_datetime in the timezone selected.

2 Likes

I actually don’t see this field in Admin --> connections. Did this menu get moved?

@law, This menu is within the connection itself by clicking the “Edit” button. It should look like this:

If you don’t see the settings for your connection, it may be a database specific limitation. Microsoft SQL Server, for example, does not natively support timezone conversions so we do not ask for these settings.

Got it, we’re using SQL Server so that makes sense.

1 Like

You may also not see this if you have user specific time zones turned on for your instance: User-Specific Time Zones (3.22+)

Hi, when I run this function CONVERT_TZ(${TABLE}.created_at,‘UTC’,‘PST’), I get an error

function convert_tz(timestamp without time zone, “unknown”, “unknown”) does not exist

When I flip the arguments, convert_timezone(‘PST’, ${TABLE}.created_at}), then it works.

Has the function changed since this discourse was written?

Hey @Charles_Benkendorf,

Each SQL dialect has its own timezone conversion functions. The order of the parameters depend on this, so it’s likely that the dialect you’re using expects something different from the example. The best way to find out is to look at the documentation provided by the dialect you’re using! Let me know if you have any questions about this.

Morgan

FYI Google Big Query Legacy does not support our database timezone feature, however if you can migrate to Big Query Standard SQL (see google’s migration docs here) you should be able to set the database timezone in admin > connections > edit

I have a different problem. I do not want Looker to convert my time stamp from UTC to my timezone? How would I tell Looker that at the development level?

Thanks!

Hi Jie,

You would want to set the parameter of convert_tz to no. Here is a helpful doc for reference: https://docs.looker.com/reference/field-params/convert_tz

Cheers,
Sami

Thank you very much!