Date dimensions with datatype: date default to not having a timezone conversion


A question that Looker Support has seen crop up is "Why is my date dimension not performing a timezone conversion?

First, you can check to see if the dimension is actually getting converted to a specific timezone in the generated SQL by clicking 'SQL' in the data tab

From there, you can look over the generated SQL to see if the conversion is being applied.

An example of a field having a timezone conversion (MySQL):

SELECT (DATE(CONVERT_TZ(`created_at`,'UTC','America/Los_Angeles'))) AS `orders.created_date`

An example of the same field not getting converted:

`SELECT (DATE(`created_at`)) AS `orders.created_date`

 

One reason why we may not see a field getting a timezone conversion is if it has datatype: date set in the field.

For example:

dimension_group: created {

type: time

timeframes: [ raw, time, date, year ]
datatype: date

sql: ${TABLE}.created_at ;;

}

 

When there is a datatype: date parameter set, Looker defaults to no timezone conversion (i.e. sets the default for convert_tz: no). If the data from the database shows just the date, as opposed to a timestamp, no timezone conversion should be performed.

Thus, if the parameter datatype: date is set (accidentally or inaccurately) for a field that has full timestamp data, we can run into this confusing situation when the timezone is not being converted when we would expect it should. If you do run into this situation, you can either set the convert_tz: yes for the field or, more accurately, you could change the datatype to meet the actual datatype for the field.


0 replies

Be the first to reply!

Reply