convert utc_departure_date field in airport local timezone in Looker view

[mohiit_jain3]

Previous year, the US based organization I work for launched in the Europe. While this was super exciting for the company, it came with a whole lot of new data modelling challenges, one of which was handling timezones.

Prior to this launch, we stored everything in UTC and set the Query Timezone to 'UTC' , for the most part it was working well.

But now as we fly the planes in North America and Europe regions both, we have different departure dates based on the timezone of the region. However, we do store our timezone data of the airports in our database.

I am struggling here to create a look in my looker dashboard where I can see the flight details, departure date(based on the local timezone of airport) and other columns based on our flights because I am only able to convert my departure date only into one specific timezone (be it Los Angeles/ UTC, Chicago etc).

But my ultimate goal is to show each flight id with its departure date based on the airport timezone (which is multiple timezones stored in my db).

dimension_group: departure {
    type: time
    timeframes: [
      raw,
      time,
      hour_of_day,
      day_of_week,
      date,
      week,
      month,
      quarter,
      year
    ]
    sql: TIMESTAMP (${TABLE}.departure_at, 'UTC') ;;
  }

I have another column which is airport_timezone which I need to embed with my departure_date_utc column so that every flight departure date reflects in its local airport timezone date and not in UTC.

would really appreciate if somebody have thoughts or any solution for this?

Special tags--

@Dawid @izzymiller @brecht @lindsey1 @er1k @rducarrouge @maxcorbin @Morgan1