Skip to main content

Time

Synced from semantic-layer

Source: docs/guide/10-time.md at origin/main (3688d236), synced 2026-11-05. Do not edit this page here; change the source and run yarn sync:semantic.

In a semantic select, time is each metric's own time axis: the moment each of its rows happened, read in the question's time zone. You bound it with a window and bucket it with DATE_TRUNC or TIME_BUCKET. Two metrics in one select may have different axes: revenue counts orders by completion and session_count counts sessions by start, and one window bounds both.

The window​

A window is half-open, time >= start AND time < end, at the top of WHERE:

SELECT MEASURE(revenue)
FROM commerce
WHERE time >= '2026-09-01' AND time < '2026-10-01'

Each bound is one of:

  • a literal: '2026-09-01', TIMESTAMP '2026-09-01 09:00:00', DATE '2026-09-01', '2026-09-01T09:00:00+05:30'
  • a parameter declared timestamp or date
  • CURRENT_DATE (midnight today) or CURRENT_TIMESTAMP (now)
  • any of these ± INTERVAL 'n' unit, in whole minutes, hours, days, weeks, months or years
SELECT DATE_TRUNC('day', time) AS day, MEASURE(order_count)
FROM commerce
WHERE time >= CURRENT_DATE - INTERVAL '7' DAY AND time < CURRENT_DATE
GROUP BY ALL

The bounds may be written either way round ('2026-09-01' <= time). Anything else on time is TIME_FILTER_FORM: one bound alone, BETWEEN, =, a bound under OR or NOT, an empty window, or a year outside 1900 to 2200.

No window means the domain's default, such as last_28d: the last 28 whole days of the domain's calendar, today included so far. The answer says it applied (semantics.window, with default set).

Only time makes a window. A condition on the time field by its own name, such as orders.completed_at >= '2026-07-01', is an ordinary field filter. The default window still applies on top of it, so a July question asked that way in November returns nothing, with no error. Bound time instead. If an answer comes back empty, check semantics.window: default set means your bounds were not read as the window.

A metric with no time axis cannot be bounded. A stated window over it is FIELD_NOT_REACHABLE. Under the default window it is answered over all time, and the answer carries a WINDOW_NOT_APPLIED warning naming it, so the all-time column is never mistaken for a windowed one.

Buckets​

DATE_TRUNC(grain, time) groups by a calendar unit:

GrainStarts at
minute, hour, daythe start of that minute, hour or local day
weekMonday, local midnight
month, quarter, yearthe 1st of the month, of January, April, July or October, or of January

TIME_BUCKET(INTERVAL 'n' MINUTE | HOUR, time) gives sizes in between: 5, 15 or 30 minutes, or 3, 6 or 12 hours. Every size divides a day, so buckets start at local midnight and every n after.

SELECT TIME_BUCKET(INTERVAL '30' MINUTE, time) AS slot, MEASURE(order_count)
FROM commerce
WHERE time >= :since AND time < :until
GROUP BY ALL

There is no second grain, no 20-minute or 2-hour bucket, and no 2-month or fiscal one. These are GRAIN_NOT_SUPPORTED, and the error names the nearest sizes. One select buckets at one grain, and the bucket must be a GROUP BY key. Bare time in the select list is refused: bucket it.

A bucket with no rows is not in the answer. An hour with no orders is a missing row, not a row with 0. Before you chart a series or compare it bucket by bucket, fill the gaps yourself: a missing count is 0, and a missing sum has no value.

An axis has a floor. A Date axis buckets by day and coarser only, and so does a DateTimeTz field declared resolution: day. Asking for hours there is GRAIN_NOT_SUPPORTED, naming the axis and the grains it offers. A window bound inside a day on such an axis is widened to whole days, and the answer says snapped: true.

The reporting time zone​

Every question is read in one zone, the reporting zone: the request's timezone, else the domain's, else UTC. It decides:

  • what a bare bound or filter value means: '2026-09-01' is midnight there
  • where days, weeks and months start: a bucket is cut on that zone's calendar
  • what CURRENT_DATE and the default window mean

A value with an offset ('2026-09-01T00:00:00+05:30') is that exact instant in any zone. Every moment in an answer is written in the reporting zone with its offset, for example 2026-09-01T00:00:00+05:30 for Kolkata. A Date stays a date. The answer says which zone it used and where it came from (semantics.timezone, {zone, source}).

timezone must name a place: Asia/Kolkata, America/New_York, UTC. IST, EST and +05:30 are refused, with the fix.

Daylight saving shows in the series. In America/New_York the spring-forward hour has no bucket, and the two passes of the fall-back hour fold into one bucket of two hours.

Time in the outer query is UTC​

Outside a semantic select, a time column is an instant, and SQL there sees it in UTC. CAST(time AS DATE) over the Kolkata bucket 2026-01-01T02:00:00+05:30 gives 31 December, the UTC date. Do calendar work in the semantic select, with DATE_TRUNC, and the outer query will never need to. EXTRACT and AT TIME ZONE are refused for the same reason.