Trunc_days() in bigquery with time zones

bug

(Ravi Mody) #1

This is an issue with a custom dimension using bigquery. I am using trunc_days() with a new york time zone:

trunc_days(
add_days(
  -1 * mod(diff_days(date(2008,01,06), now()), 7), now()
))

This is evaluating to [2018-11-03 20:00:00] instead of [2018-11-04 00:00:00]. I believe this is a bug?


(leticia.esparza) #2

Hey @ravimody,

It sounds like the custom dimension is behaving due to the effects of the query timezone conversions. Here is an example that illustrates how time zones affect your current custom dimension:

My example here is a custom dimension based off of the same logic. Notice that when we change the query time zone to UTC, that custom dimension behaves as we expect. This is because my database timezone is UTC and changing the timezone of this query to UTC results in no timezone conversions.

We have a few options to get the results you would like:

  • If you have a Query Timezone set, we can leverage the convert_tz parameter. This would require creating a LookML dimension based off of similar logic and then setting convert_tz: no to declare that we do not want Looker to perform a time zone conversion for this particular dimension.

  • If you have User Specific Timezones enabled, we can select UTC as the query timezone using our timezone dropdown menu at the top right corner of the explore to the left of the Run button. This is the method I used in the above gif example.

Let me know if I can clarify anything about this on my end! If you would like us to take a closer look at your example, feel free to reach out via email at support@looker.com or via chat!

Cheers,

Leticia