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

[mohiit_jain3]

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 

[mohiit_jain3]

@fabio1 @vfx1 @izzymiller ? anyone else maybe?

[mohiit_jain3]

@Dawid any idea about this?

[fabio1]

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.

[mohiit_jain3]

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

[vfx1]

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.

[mohiit_jain3]

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 

[fabio1]

@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

[vfx1 · Accepted solution]

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

[mohiit_jain3]

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!!

[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

[mohiit_jain3]

copy, really helpful. Thanks @fabio1 

[vfx1]

@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

@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 🙂

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.

[fabio1]

@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