Time
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
timestampordate CURRENT_DATE(midnight today) orCURRENT_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:
| Grain | Starts at |
|---|---|
minute, hour, day | the start of that minute, hour or local day |
week | Monday, local midnight |
month, quarter, year | the 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_DATEand 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.