UTC date and hour data to different timezone based report

[Pritam_Sarkar]

Need help with converting UTC date partitioned data into a different timezone based data/report.

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

I have a date field (UTC) and event_hour (string) in my view/redshift table for some aggregated data.
Data is partitioned by date key and aggregated by event_hour dimension.

I am trying to use the date and hour information to get data for another timezone e.g: EST.

e.g: when I filter for last two days ‘2020-03-22’, ‘2020-03-23’, the query should automatically be constructed for the date and hour range ‘2020-03-22’ 4am to ‘2020-03-24’ 4am.

view: metrics_daily {
sql_table_name: <redshift_table_name> ;;

dimension_group: date_key {
type: time
timeframes: [
raw,
hour_of_day,
time,
date,
week,
day_of_week,
week_of_year,
month,
month_name,
quarter,
year,
day_of_month
]
convert_tz: no
sql: ${TABLE}.date_id ;;
}

dimension: event_hour {
type: string
sql: ${TABLE}.event_hour ;;
}
… other dimension and measures
}

Explore: With always_filter on date_key_date

explore: metrics_daily {
always_filter: {
filters: {
field: date_key_date
value: “8 days ago for 7 days”
}
}

how can we achieve this in looker. Any help is appreciated.