Time intelligence

This guide explains the authoring format and semantics. Target support varies; see Support and limitations.

This document defines Semantic Hub time fields, effective time context, calendars, time spines, cumulative frames, missing-period behavior, and period-over-period alignment. It complements YAML model format, Metric evaluation and composition, and Grain and multi-fact query planning.

The JSON Schemas remain authoritative for authored shape. A compiler must reject a temporal operation when it cannot preserve calendar, time-zone, grain, completeness, missing-period, or base-metric semantics exactly.

1. Temporal concepts

Semantic Hub distinguishes:

Time intelligence never treats database session settings as semantic model state.

2. Time fields and datasets

2.1 Field requirements

A structural time reference resolves an authored field object. It never falls back to a same-name physical column. The field must have a temporal data_type compatible with the operation. A field's preferred_usage does not affect eligibility.

Field.time_type classifies the authored grain:

time_type is a classification, not permission to reinterpret an integer as a date. The field expression, data type, and calendar mapping must agree.

HALF_YEARS is available as authored field metadata, but the current TimeContext.supported_granularities, cumulative units, and period-over-period comparison grains do not define a half-year operation. A half-year field can still be selected and grouped as an ordinary field; a compiler must not invent a HALF_YEAR metric granularity.

2.2 TIME datasets

dataset_type: TIME identifies a dataset designed to supply calendar positions and attributes. It follows ordinary dataset contracts:

• it has a declared source and authored fields;
• a non-null unique key defines each spine row;
• joins and field references are explicit;
• dataset filters are intrinsic and cannot be bypassed; and
• its grain must be dense enough for every advertised calendar operation.

name: dates
dataset_type: TIME
table: DATES
unique_keys: [{ columns: [DATE_ID] }]
fields:
  - name: date_value
    data_type: DATE
    expression: DATE_VALUE
    time_type: DAYS
  - name: month_start
    data_type: DATE
    expression: MONTH_START
    time_type: MONTHS

A regular fact dataset can expose temporal fields and time-aware metrics. It does not become a time spine merely because it contains dates.

2.3 Dataset defaults

Dataset.default_time_dimension is inherited only where the consuming metric property permits a default. CUMULATIVE.time_dimension and PERIOD_OVER_PERIOD.time_dimension are required by Metrics.json, so authors cannot omit them in those metric types.

Dataset.time_zone supplies the zone for temporal values anchored in that dataset when a metric does not override it.

3. Effective time context

The compiler resolves each temporal operation independently. Explicit operator properties always win.

3.1 Time dimension

Resolution is:

1. the metric-type-specific time_dimension, when present;
2. Metric.time_context.time_dimension when the metric type permits a contextual default;
3. the applicable metric-bearing dataset's default_time_dimension; or
4. an error when the operation requires a time field and none resolves uniquely.

The resolved field carries stable object identity, anchor dataset, relationship path, type, nullability, and authored time grain.

3.2 Calendar

Resolution is:

1. Metric.time_context.calendar;
2. SemanticModel.time_settings.default_calendar; or
3. the operation's defined Gregorian behavior when it requires only standard civil periods and no named custom attributes.

A referenced calendar must exist in the selected semantic model. CUSTOM behavior and any fiscal or spine-specific attribute require a named calendar.

3.3 Time zone

Resolution is:

1. Metric.time_context.time_zone;
2. the time_zone of the dataset anchoring the resolved time field;
3. SemanticModel.time_settings.default_time_zone; or
4. no conversion for zone-free DATE values.

An instant-bearing timestamp operation without a resolvable zone is invalid when target session time could change its local date or period. Authors should declare UTC explicitly when UTC is the business clock.

3.4 Missing-period policy

Resolution is:

1. Metric.time_context.fill_missing_periods; then
2. SemanticModel.time_settings.fill_missing_periods; then
3. schema default SKIP.

3.5 Supported granularities

Metric.time_context.supported_granularities restricts valid query grains to the listed values: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND. It does not synthesize calendar attributes or authorize target approximations.

A query requesting an unlisted grain fails with the metric, requested grain, and supported list in the diagnostic.

4. Calendar definitions

time_settings:
  default_time_zone: America/Los_Angeles
  default_calendar: corporate_fiscal
  fill_missing_periods: SKIP
  calendars:
    - name: corporate_fiscal
      calendar_type: FISCAL
      week_start: MONDAY
      fiscal_year_start_month: 2
      fiscal_year_start_day: 1
      time_spine_dataset: calendar
      time_spine_dimension: date

Calendar names are unique within one semantic model.

4.1 Calendar types

fiscal_year_start_month is required for FISCAL and ranges from 1 through 12. A start day must exist for every applicable start year; definitions such as February 30 are invalid.

The model must not use fiscal properties to redefine an ISO_8601 or ordinary GREGORIAN calendar. Target-native week or fiscal functions are usable only when they reproduce the named calendar exactly.

4.2 Week semantics

week_start defaults to MONDAY. For Gregorian and fiscal calendars it defines the first local day of a week. For ISO 8601 it is fixed to Monday and does not change ISO week-year numbering.

Week identity includes its week year where needed. Grouping only by week number can merge weeks from different years and is invalid unless another grouping field determines the year.

4.3 Fiscal labels

A fiscal period has both a boundary and a label. Implementations must use one documented labeling convention consistently, including whether a fiscal year is named for its starting or ending Gregorian year. When a time spine supplies the fiscal label, that authored attribute is authoritative.

The compiler explanation reports the resolved fiscal start, label field or convention, and period boundaries. It must not silently inherit a database-specific fiscal convention.

5. Time spines

5.1 Anchor and density

Calendar.time_spine_dataset selects the dense calendar dataset. time_spine_dimension resolves relative to that dataset and identifies its ordered date or timestamp field. If time_spine_dataset is present, the dimension is required.

A time_spine_dimension without time_spine_dataset has no anchor and is a semantic validation error.

Semantic validation proves:

• the dataset is in model scope and has dataset_type: TIME;
• the spine field is authored, temporal, non-null, and uniquely ordered at its declared grain;
• no required position is duplicated;
• the configured coverage interval has no gaps at the base spine grain;
• coarser period identifiers and boundaries are internally consistent; and
• custom period order is total and deterministic.

5.2 When a spine is required

A spine is required for:

• CUSTOM calendars;
• fill_missing_periods: NULL or ZERO when the target cannot generate the exact period series independently;
• calendar completeness checks not derivable from the fact data;
• custom fiscal periods such as 4-4-5 calendars;
• period attributes or labels with no canonical arithmetic definition; and
• row-based frames whose positions must include missing periods.

Gregorian, ISO, or simple fiscal operations may use exact target-native date arithmetic without a physical spine. The selected strategy must be capability-tested and produce the same period identities.

5.3 Join behavior

The output period domain comes from the spine. Facts join to periods using the effective local temporal value and calendar rules. Missing fact rows must not remove spine rows.

A spine join does not authorize fact fanout. One source event maps to at most one period at the selected grain. If overlapping custom periods allow several matches, the calendar is invalid for ordinary aggregation unless an explicit allocation contract is added in a future schema.

5.4 Coverage

The compiler verifies that the spine covers the requested output periods plus every lookback or lookahead required by cumulative frames and offsets. An uncovered period is a coverage error, not automatically a missing business period.

6. Time-zone semantics

6.1 Order of operations

For an instant-bearing timestamp:

1. interpret the source value according to its declared source type and connection contract;
2. convert the instant to the effective IANA time zone;
3. derive local date and clock components;
4. map the local value to the selected calendar period; and
5. truncate, group, offset, or compare using that period identity.

Applying a time zone after truncation is incorrect near local boundaries.

6.2 Daylight-saving transitions

Calendar days are local civil days. They may contain 23 or 25 hours. A trailing seven-calendar-day frame is not necessarily 168 elapsed hours.

Ambiguous or nonexistent local timestamps must follow the source connection's declared timestamp interpretation and the target capability mapping. A compiler must not rely on an undocumented database default.

6.3 Date-only values

DATE has no instant or time-zone shift. A zone can still be relevant when a DATE is compared with or derived from an instant, but converting a standalone date must not move it to a different civil day.

7. Query time filters and source expansion

A query time predicate normally restricts the output period domain. A time-aware metric may require source rows outside that visible range.

For a query showing March with a trailing 30-day metric, the compiler:

1. retains March as the output domain;
2. expands the metric's source interval into February as required;
3. applies immutable dataset and security filters to all expanded source rows;
4. applies the metric's effective non-time query filters and intrinsic filters; and
5. trims only the final period rows back to March.

Blindly pushing date >= March 1 into the source scan would truncate the first rolling frames and is invalid.

A time predicate that is part of an intrinsic dataset, security, aggregate, metric-input, or static filter-context rule is not merely an output bound. It continues to restrict the metric's eligible source population according to its scope.

8. Cumulative frames

A CUMULATIVE metric orders a base metric by its required time_dimension and evaluates one declared frame per output position. Running totals ordered by non-temporal dimensions or metrics over grouped query rows are modeled as WINDOW metrics with function: SUM rather than CUMULATIVE.

8.1 Frame types

For TRAILING and LEADING, valid units are DAY, WEEK, MONTH, QUARTER, and YEAR. CUSTOM_ROWS requires ROW. Parameters that do not apply to the selected frame are semantic errors even when basic schema validation permits their presence.

TRAILING with value: 7, unit: DAY, and default include_current: true contains the current local calendar day and six prior days.

8.2 Partitions and order

partition_by_dimensions divides the time series into independent populations, such as one series per region. The time dimension supplies primary order. If it is not unique within the partition at the effective base grain, the planner aggregates to one value per period or requires a deterministic additional contract; physical row order is never used.

8.3 Accumulation

Daily distinct-user counts cannot be summed into a seven-day distinct-user count. Use REEVALUATE_BASE so users are deduplicated over the complete seven-day event population.

8.4 Coarser display grain

period_aggregation selects how a cumulative series is represented at a coarser query grain:

• FIRST_VALUE or LAST_VALUE selects the boundary series value;
• AVERAGE averages series positions; and
• MIN or MAX selects an extreme series value.

It does not change the cumulative frame itself. Selection must use calendar order and preserve missing versus present-null positions.

9. Missing periods

fill_missing_periods controls absent period rows, not null values in existing rows.

Filling occurs before CUSTOM_ROWS, lag/lead positions, or other row-position operations when those positions are intended to represent consecutive periods. Time-interval frames use calendar boundaries and do not reinterpret a skipped period as an adjacent unit.

ZERO does not replace:

• a present period whose non-empty metric result is null;
• an empty aggregate unless its own empty_result says zero;
• a missing period outside spine coverage; or
• a period excluded by security or an intrinsic dataset filter.

The plan may carry period_present, population_present, and scalar value separately.

10. Period-over-period alignment

A PERIOD_OVER_PERIOD metric evaluates the base metric at a comparison grain, aligns a current period with one nonzero offset period, and applies one comparison.

- name: revenue_yoy_growth
  metric_type: PERIOD_OVER_PERIOD
  base_metric: { metric_ref: total_revenue }
  time_dimension: order_date
  offset:
    value: -1
    unit: YEAR
    alignment: CALENDAR
  comparison: PERCENT_CHANGE
  comparison_grain: MONTH
  complete_periods_only: true

10.1 Offsets

TimeOffset.value cannot be zero. Negative values look backward and positive values look forward. Units are DAY, WEEK, MONTH, QUARTER, and YEAR.

Calendar alignment is the default. A simple LAG(n) is equivalent only when the period series is dense and the offset maps to exactly n rows under the selected calendar.

10.2 Comparison grain

comparison_grain can be YEAR, QUARTER, MONTH, WEEK, or DAY. When omitted, the compiler derives one unambiguous grain from the selected time field and query. If several grains are possible or the query grain is finer than the calendar can align, validation fails rather than selecting one arbitrarily.

The base metric is evaluated independently for current and offset periods at this grain before comparison.

10.3 Comparison operations

Let current be the current-period value and offset the aligned value.

divide_by_zero is valid only for RATIO and PERCENT_CHANGE. A null offset value propagates normally and is not a zero denominator.

10.4 Complete periods

With complete_periods_only: true, both the current and aligned comparison periods must be complete under the named calendar and the query's as-of boundary. Completeness comes from the time spine or an exact calendar/clock proof, not from the mere presence of fact rows.

For example, comparing a partial current month with a complete prior month is excluded unless a separately modeled same-progress comparison contract exists. The current schema does not define that approximation.

10.5 Missing aligned period

missing_period_result applies when the aligned period identity does not exist. It is distinct from:

• an existing aligned period with no fact rows;
• an existing period whose metric value is null;
• a non-null zero comparison value; and
• time-spine coverage failure.

The compiler retains period-presence state until this policy is applied.

11. Time-aware filter and grain interactions

• filter_context may remove query filters from a metric, but it does not remove dataset or security filters.
• A metric-input offset shifts that input's time evaluation; it does not rewrite sibling inputs or the final query grain.
• Fixed-grain metrics with a time dimension must state whether time is retained, excluded, broadcast, or recomputed under the ordinary grain contract.
• Multi-fact time composition resolves the calendar role and time path independently for every fact branch. Similar field names do not prove comparable periods.
• Role-playing dates such as order date and ship date require explicit relationship paths and cannot be substituted for each other.

12. Target capability contract

Snowflake, PostgreSQL, DuckDB, Oracle, and Databricks each have their own temporal capability definition. DuckDB is not a PostgreSQL alias.

The target contract covers:

• IANA time-zone conversion and timestamp types;
• Gregorian, ISO, fiscal, and custom period mapping;
• week-year and week-start behavior;
• interval arithmetic for every offset and frame unit;
• date-series generation or spine joins;
• window-frame syntax and boundary behavior;
• daylight-saving ambiguity handling;
• full period completeness and missing-period filling; and
• preservation of date, timestamp, precision, null, and ordering semantics.

A compiler may use native functions, an exact rewrite, or a time-spine plan. If none preserves the canonical contract, it returns an unsupported-capability diagnostic. It must not approximate fiscal, custom, time-zone, or missing-period behavior with convenient target defaults.

13. Validation failures

Semantic Hub rejects conditions including:

• an unresolved, non-temporal, or ambiguous time dimension;
• an invalid IANA time-zone identifier;
• a missing named calendar;
• an ISO calendar with non-Monday week start;
• an invalid fiscal boundary;
• a custom calendar without an authoritative spine;
• a spine with gaps, duplicates, null positions, inconsistent attributes, or insufficient coverage;
• an unsupported requested granularity;
• invalid frame parameters or a non-ROW custom-row unit;
• a cumulative scalar rollup that is invalid for the base metric;
• a row frame whose period positions are nondeterministic;
• a zero time offset;
• ambiguous comparison grain or incompatible current and offset calendars;
• an incomplete-period comparison when complete periods are required;
• a query time filter pushed into source evaluation so that it truncates a required lookback or lookahead; or
• target temporal behavior without an exact mapping.

Diagnostics include the metric, time field and path, effective calendar and zone, output grain, frame or offset, required source interval, target when applicable, and concrete remediation.

14. Explainability and conformance

An explain result exposes:

• the effective time field, calendar, zone, missing-period policy, and grain for every metric branch;
• local and instant boundaries used for source evaluation;
• the selected spine and validated coverage;
• output-domain predicates versus expanded source predicates;
• cumulative partition, order, frame boundaries, and accumulation strategy;
• whether the base metric is recomputed or combined from sufficient state;
• synthesized periods and their NULL or ZERO policy;
• current and offset period identities, comparison grain, completeness, and division behavior; and
• target-native functions or rewrites used.

A conforming implementation has positive and unsupported-capability tests for every calendar type, frame type, accumulation, missing-period policy, comparison, offset alignment, and typed SQL target. Fixtures cover daylight-saving boundaries, leap days, ISO week years, fiscal year boundaries, absent periods, present-null periods, partial periods, and source lookback expansion.