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

Looker Q&A Looker
,
1

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

2

@fabio1 @vfx1 @izzymiller ? anyone else maybe?

3

@Dawid any idea about this?

4

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:

  • Try using datatype: datetime. In theory, this should let you consume a column whose BQ type is DATETIME, though I recall there being some unexpected/missing functionality there which I’m not sure if it was ever resolved
    https://docs.looker.com/reference/field-params/datatype
  • Try making your own string-based representation
  • Probably a bad idea: Try converting the column to a modified point in time that would have the right datetime in some reference timezone, like UTC, and then working with the field as a timestamp but with convert_tz: no. Fair warning, every time I tried to use convert_tz: no I concluded that it does not make sense to use it ever, but maybe this use case does, I’m not sure.
5

Let me try these out @fabio1 , thank you for the recommendations!

6

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.

7

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);;

** @fabio1 **

8

@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

9

@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 :slight_smile: 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.

10

Sounds great @vfx1 @fabio1 . Really appreciate you guys!

I was wondering if I have to use the datatype:date then I won’t be able to use the dimension group right? I can only create a normal dimension out of it!!

11

@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

12

copy, really helpful. Thanks @fabio1

13

@fabio1

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 :slight_smile:

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.

14

@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