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.
@fabio1 @vfx1 @izzymiller ? anyone else maybe?
@Dawid any idea about this?
Unfortunately, I haven’t ever done a deep-dive on this type of use case. I suspect the conclusion may be that Looker works natively with timestamps that represent points in time, and doesn’t have a good, native abstraction for grouping together locally-notated times that are divorced from specific instants in time.
That said, here are some ideas to try:
Let me try these out @fabio1 , thank you for the recommendations!
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
Hi Mohit,
This seems to be working beautifully:
WITH a AS (
SELECT CURRENT_TIMESTAMP() as ts, 'Asia/Kolkata' as tz
)
SELECT ts, tz, DATETIME(ts, tz) as ts_tz FROM a
Now, because of the conversion to DATETIME, Looker is not going to like it. Be sure to mention datatype: date which sounds incorrect but as described in my previous post, is hack around the wrong use of Looker’s DATETIME type.
This shoud work.
Actually, the code did not give any error but I got the null values.
I tried to run this--
dimension: airport_timezone_actual_arrival {
type: date
description: "Actual arrival date (Airport timezone)"
sql: ( With a as (select ${actual_departure_raw} as ad, ${destination_airport.airport_timezone} as dt)
Select DATETIME(ad, dt) from a);;
@mohiit_jain3 I see you tagged me, but I’m not sure about that approach... that suggestion was from @vfx1
Edit: I do think in their suggestion, the CTE (the `WITH a as (...)` clause) was just for some example data, it would not actually go into your dimension, instead you would refer to your columns directly in the DATETIME call
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.
@mohiit_jain3 - You can’t use the `dimension_group: ` syntax in this case, but you CAN create multiple `dimensions:` that have the same `group_label` value, and it will result in the same output/functionality
copy, really helpful. Thanks @fabio1
@mohiit_jain3 - You can’t use the `dimension_group: ` syntax in this case, but you CAN create multiple `dimensions:` that have the same `group_label` value, and it will result in the same output/functionality
Fabio, I think my hack works with dimension group quite well. See the following example:
view: test_ts_tz {
derived_table: {
sql: SELECT
current_timestamp as ts,
'Asia/Kolkata' as tz
;;
}
dimension_group: ts_tz {
type: time
datatype: date
timeframes: [
time,
hour,
date,
week,
month
]
sql: DATETIME(${TABLE}.ts, ${TABLE}.tz) ;;
}
}
Then with an empty explore, select any time dimension, like this:
And here’s the SQL to prove it actually does it 🙂
WITH test_ts_tz AS (SELECT
current_timestamp as ts,
'Asia/Kolkata' as tz
)
SELECT
FORMAT_TIMESTAMP('%F %T', CAST(DATETIME(test_ts_tz.ts, test_ts_tz.tz) AS TIMESTAMP)) AS test_ts_tz_ts_tz_time,
FORMAT_TIMESTAMP('%F %H', CAST(DATETIME(test_ts_tz.ts, test_ts_tz.tz) AS TIMESTAMP)) AS test_ts_tz_ts_tz_hour,
CAST(CAST(DATETIME(test_ts_tz.ts, test_ts_tz.tz) AS TIMESTAMP) AS DATE) AS test_ts_tz_ts_tz_date
FROM test_ts_tz
GROUP BY 1,2,3
ORDER BY 2 DESC
LIMIT 500
So once again, specifying datetime: date deals with the years old looker bug when working with bigquery datetime.
I hope this is useful.
@vfx - you’re very right! I had gotten confused and thought we were talking about `type: date`, not `datatype: date`. As long as it is `type: time`, then you’re right, dimension_group is supported