Min/Max of a Timstamp Breaks Timezone Conversion

Looker Q&A Looker
,
1

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