Building a new time field for Timezone conversion in Looker

[antho]

Hello all,

I’m facing a problem I’m not sure how to solve in Looker.

I have a table of data that includes the following fields:

---

| date (DATE) | hour (STRING) | sales (NUMBER)| timezone (STRING) |

| 2019-12-27 | 10 | 120 | Europe/Paris|

I have multiple different timezones inside this table and at the end, my goal is to be able to report on a single standard timezone using the default Looker dropdown so that we can compare all countries with a unique and selectable timezone.

Do you have any idea of how to approach this?
Thanks a lot 😉

Anthony

[Quinn_Wildman]

I’d create a dimension which combines date, hour and timezone. This is a redshift example:

dimension: some_field
{
type:date_time
sql: convert_timezone(timezone_field, dateadd(h, hour_field, date_field));;
}

[antho]

Hello Quinn,

Thanks a lot for this! I’ll give it a try (We are on Snowflake!).

Have a great day,
Anthony