Min/Max of a Timstamp Breaks Timezone Conversion

In our looker instance, we have a view that takes the min/max of a datetime:

view: view_name {
derived_table: {
sql: SELECT *,
min(date_field) as Earlist_Time,
max(date_field) as Last_Time
FROM TABLE;;
..................
...................
dimension_group: Last_Time {
type: time
description: "Most recent time"
timeframes: [
raw,
time,
hour_of_day,
date,
day_of_week,
week,
month,
quarter,
year,
day_of_week_index
]
sql:${TABLE}.Last_Time;;
}
....................
....................
}

When using the Last_Time.date dimension (when converting UTC->EST/CST) Looker will return a date that is one day earlier, regardless of the actual timezone conversion.

Example:

  • 9/5 @17:00 UTC → 9/4 EST
    • 9/5 @17:00 UTC → 9/5 @ 12:00 EST → 9/5 EST
  • 9/5 @ 1:00 UTC → 9/4 EST
    • 9/5 @1:00 UTC → 9/4 @ 20:00 EST → 9/4 EST
0 0 73
0 REPLIES 0
Top Labels in this Space
Top Solution Authors