Solved: Convert UTC to local time zone in Looker (have big... - Page 2

mohiit_jain3 · 02-24-2022 10:06 AM

I am not able to convert my UTC timestamp column to a local destination timezone using a different column in my BigQuery database.

The complete scenario is, I have a column timezone in one my destination table in which I am trying to convert my current utc_timestamp column which is in UTC.

dimension_group: timestamp_actual {
type: time
timeframes: [
raw,
time,
date,
week,
month,
quarter,
year
]
description: "Actual date (destination timezone)"
sql: DATETIME(${utc_timestamp}, ${destination.timezone});;

would really appreciate if somebody have thoughts or any solution for this?

any thoughts

vfx1

@mohiit_jain3 @fabio1

Of course Fabio is right - my example was just a template assuming you have a source data of two columns - one ts (timestamp) and one bigquery compatible timezone tz (string).

Then putting the following statements should work
sql: DATETIME(${TABLE}.ts, ${TABLE}.tz) ;;
datatype: date

And btw “datatype: datetime” (bigquery) never worked for me, ever 🙂 it surely produces wrong SQL. I ended up converting everything to timestamp OR using the hack described above.

Of course I can be getting it all wrong but for the last few years, couldn’t find a better approach.

View solution in original post

Convert UTC to local time zone in Looker (have bigquery database)