Vistelio | Dates, Timezones, and Fiscal Calendars in LookML: Getting Time Right

Nothing generates more "the dashboard is wrong" tickets than time. The revenue number is right, but it lands on the wrong day. Last month closes on the 28th for finance and the 31st in Looker. A user in Sydney sees yesterday's total change when a colleague in London opens the same Look. None of this is a bug in Looker; it is almost always a modelling decision that nobody made explicitly.

This tutorial walks through modelling time properly in LookML: dimension groups, the three timezones that decide what a "day" is, fiscal calendars, week boundaries, date spines, and period-over-period comparisons. These patterns are more important than they used to be, because natural-language layers on top of the semantic model — Conversational Analytics, Gemini in Looker, an MCP client — will confidently answer "how did we do last quarter?" using whatever calendar your LookML happens to encode. If the calendar is wrong, the AI is wrong, fluently.

Start with dimension_group, not dimensions

The first rule: never hand-roll a set of date dimensions. Use a dimension_group of type: time and let Looker generate the timeframes.

view: orders {
  dimension_group: created {
    type: time
    timeframes: [
      raw,
      time,
      date,
      week,
      month,
      month_name,
      quarter,
      fiscal_quarter,
      fiscal_year,
      year
    ]
    convert_tz: yes
    datatype: timestamp
    sql: ${TABLE}.created_at ;;
  }
}

A few things worth knowing about that block:

The group is named created, not created_at and not created_date. Looker appends the timeframe, so the field names become orders.created_date, orders.created_month, and so on. Naming it created_date gives you orders.created_date_date.
Reference the group in other LookML with ${created_raw} when you need the untouched database column (for joins, date math, and sql_always_where), and ${created_date} when you want the converted, truncated value.
Trim the timeframes list to what people actually use. Every timeframe is a field in the field picker, and a group with twenty timeframes across thirty views is how an explore becomes unusable.
Set datatype: explicitly when the underlying column is a date or epoch rather than a timestamp. Getting this wrong is the classic cause of an off-by-one-day error.

The three timezones

Looker has three separate timezone settings, and understanding the chain is the difference between a five-minute fix and a week of arguing.

Database timezone — what timezone the raw timestamps are stored in. Set per connection in Admin > Connections.
Looker application timezone — the instance-level default used for scheduling and system fields.
Query timezone — the timezone results are converted to when a query runs. This can follow the user (Viewer time zone), be pinned to a single zone, or be set per user.

When convert_tz: yes (the default), Looker wraps the column in a conversion from database timezone to query timezone before truncating it to a day, week, or month. That conversion is what makes "yesterday's revenue" differ between a London and a Sydney viewer: the day boundary moved.

Set convert_tz: no when the column is not a real moment in time:

dimension_group: invoice_period {
  type: time
  timeframes: [date, month, quarter, year]
  convert_tz: no
  datatype: date
  sql: ${TABLE}.invoice_period ;;
}

A date-typed column, a business date agreed with finance, or an already-localised reporting date should never be converted. If you convert a plain DATE column, some warehouses will implicitly cast it to midnight UTC first and then shift it backwards, and your March 1 invoices quietly become February 28.

Our rule of thumb on audits: event timestamps convert, business dates do not. Write that decision into a comment next to the dimension group so the next developer does not flip it back.

One more trap: filters follow the same conversion. A dashboard filtered to "yesterday" in a viewer-time instance is a different SQL predicate for different users. If finance needs one immutable answer, pin the query timezone for that model's users or expose an explicit business-date field.

Fiscal calendars

Most companies do not run on the Gregorian calendar. Looker supports fiscal periods natively, but only if you tell the model when the year starts.

model: retail {
  connection: "warehouse"
  include: "/views/*.view.lkml"

  fiscal_month_offset: 3
}

fiscal_month_offset: 3 means the fiscal year starts in April: the offset is the number of months the fiscal year is ahead of the calendar year. Use -3 for an October start. Once it is set, add the fiscal timeframes to your dimension groups:

timeframes: [date, week, month, fiscal_month_num, fiscal_quarter, fiscal_quarter_of_year, fiscal_year]

Two cautions:

The offset is model-wide. If group finance runs an April year and a recently acquired subsidiary runs a January year, you need two models (or a _fiscal refinement layer over shared views), not one clever dimension.
fiscal_month_offset only handles month-aligned fiscal years. Retail 4-5-4, 13-period, and ISO-week calendars are not expressible this way. For those, build a date dimension table in the warehouse and join it.

The date dimension table pattern

For anything more complex than a month offset, a physical calendar table is the honest answer:

view: date_dim {
  sql_table_name: analytics.date_dimension ;;

  dimension: date_key {
    primary_key: yes
    type: date
    convert_tz: no
    datatype: date
    sql: ${TABLE}.date_key ;;
  }

  dimension: retail_week { type: number  sql: ${TABLE}.retail_week ;; }
  dimension: retail_period { type: string sql: ${TABLE}.retail_period ;; }
  dimension: retail_year { type: number  sql: ${TABLE}.retail_year ;; }
  dimension: is_holiday { type: yesno    sql: ${TABLE}.is_holiday ;; }
  dimension: is_trading_day { type: yesno sql: ${TABLE}.is_trading_day ;; }
}

Join it on the raw date, one row per calendar day, relationship: many_to_one:

explore: orders {
  join: date_dim {
    type: left_outer
    relationship: many_to_one
    sql_on: ${orders.created_date} = ${date_dim.date_key} ;;
  }
}

Because it is many_to_one, it introduces no fanout, and every fiscal, retail, and holiday attribute becomes a normal dimension your users can group by — and a normal field a natural-language agent can find.

Week boundaries

week_start_day is set on the model and defaults to Monday:

week_start_day: sunday

It sounds trivial until you compare a Looker weekly chart against a warehouse query that used the default DATE_TRUNC(..., WEEK) — which is Sunday-based in BigQuery and Monday-based in Snowflake by default. Half of all "Looker disagrees with our SQL" tickets are this. Decide once, set it in the model, and make sure any hand-written SQL in derived tables uses the same convention.

Period-over-period comparisons

There are three approaches, in increasing order of power and effort.

1. Native timeframe comparison. Group by created_month and use Looker's table calculations or the built-in period-over-period options in the Explore UI. Fine for ad hoc analysis; not reusable.

2. Period-offset dimensions. Model a "days into period" dimension so current and prior periods can be plotted on the same axis:

dimension: days_into_month {
  type: number
  sql: DATE_DIFF(${created_date}, DATE_TRUNC(${created_date}, MONTH), DAY) ;;
}

dimension: month_offset_from_today {
  type: number
  sql: DATE_DIFF(
         DATE_TRUNC(CURRENT_DATE(), MONTH),
         DATE_TRUNC(${created_date}, MONTH),
         MONTH) ;;
}

Filter to month_offset_from_today: 0, 1, 12, pivot on it, and plot against days_into_month. You get this-month vs last-month vs same-month-last-year on one chart with no table calculations.

3. Measures with filtered aggregation. When you want a single row with both numbers:

measure: revenue_current_period {
  type: sum
  sql: ${amount} ;;
  filters: [month_offset_from_today: "0"]
  value_format_name: usd
}

measure: revenue_prior_period {
  type: sum
  sql: ${amount} ;;
  filters: [month_offset_from_today: "1"]
  value_format_name: usd
}

measure: revenue_growth_pct {
  type: number
  sql: SAFE_DIVIDE(${revenue_current_period} - ${revenue_prior_period},
                   ${revenue_prior_period}) ;;
  value_format_name: percent_1
}

The catch: these measures only make sense if the query's date filter is wide enough to contain both periods. Guard them with an always_filter on the explore, or a Liquid-driven sql_always_where, so a user cannot filter to one month and then wonder why the prior-period column is blank.

Sparse data and date spines

Aggregations only return rows that exist. If nothing sold on a Sunday, the Sunday is missing, and the line chart draws a straight line across it — or worse, a moving average silently computes over the wrong window.

The fix is to drive the query from the calendar rather than the fact table:

explore: date_dim {
  label: "Daily Activity"
  join: orders {
    type: left_outer
    relationship: one_to_many
    sql_on: ${date_dim.date_key} = ${orders.created_date} ;;
  }
}

Every day in the range now appears, with nulls where there was no activity. Use COALESCE in the measures if you need zeros instead of blanks. If you do not have a calendar table, generate one once with GENERATE_DATE_ARRAY (BigQuery) or a recursive CTE and persist it — it is a handful of thousand rows and it will earn its keep.

Timeframes in filters and Liquid

When you need the user's selected date range inside SQL — for an incremental PDT bound, a window function, or a templated filter — use date_start and date_end on a filter, not the dimension itself:

filter: reporting_period {
  type: date
}

dimension: in_reporting_period {
  type: yesno
  sql: {% condition reporting_period %} ${created_raw} {% endcondition %} ;;
}

Note ${created_raw}: conditions must compare against the raw column so the warehouse can use the partition or index. Comparing against a converted, truncated timeframe defeats partition pruning and, on a partitioned BigQuery table, turns a cheap query into a full scan.

A checklist before you ship a time model

Every dimension group has an explicit datatype: and a deliberate convert_tz: value.
week_start_day and fiscal_month_offset are set in the model file, not assumed.
The query timezone policy is documented, and finance-facing content uses a fixed business date.
Filters and PDT bounds reference _raw fields so partition pruning still works.
Sparse-data dashboards are driven from a date dimension, not the fact table.
A LookML data test asserts that last month's revenue matches a known finance figure, so a future timezone change fails CI instead of a board meeting.

test: revenue_matches_finance_close {
  explore_source: orders {
    column: total_revenue {}
    filters: [orders.created_month: "2026-01"]
  }
  assert: january_revenue_is_correct {
    expression: ${orders.total_revenue} = 4821330 ;;
  }
}

Wrapping up

Time modelling is unglamorous and it is where trust in a Looker deployment is won or lost. Decide the timezone policy, encode the fiscal calendar in the model or in a calendar table, standardise week starts, and pin the answers with data tests. Do that once and the reconciliation arguments stop.

If your Looker instance already has a time problem you have been working around with table calculations and manual exports, our team untangles this kind of thing regularly as part of a Looker health check and technical audit. Get in touch with the details of your setup and we will tell you what we would change.