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
Solved! Go to Solution.
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.