Time storage format (D-FORMAT)¶

Where: a time Field's custom_extensions. Payload key: time, an object with format, storage and granularity. Default (absent): the column is assumed to be a native DATE / TIMESTAMP. Impact if ignored: silent — list it in requires.

When to configure it¶

Use D-FORMAT when a time field stores dates as strings or integers, such as '20260907' or 202609. Native DATE/TIMESTAMP fields normally need no configuration: that is the default when time.format is absent.

time.format describes the stored encoding, not the output display format. Declare it in a field's DATUS custom_extensions. Its effective is_time must be true, either explicitly or from the core datatype default. The declaration describes the result of the field's expression, which may be a calculation rather than a source column.

Quick start¶

Within a dataset's fields, declare a string date and an integer month as follows. storage is inferred from datatype: String gives "string", Integer gives "int". Without datatype, it still defaults to "string". Include "time" in requires so older Datus-mode engines that do not support it reject the model instead of ignoring it.

fields:
  - name: dt
    expression:
      dialects: [{dialect: ANSI_SQL, expression: dt}]
    datatype: String
    dimension: {is_time: true}
    custom_extensions:
      - vendor_name: DATUS
        data: '{"v": "1.6", "requires": ["time"], "time": {"format": "%Y%m%d"}}'
  - name: etl_month
    expression:
      dialects: [{dialect: ANSI_SQL, expression: etl_month}]
    datatype: Integer
    dimension: {is_time: true}
    custom_extensions:
      - vendor_name: DATUS
        data: '{"v": "1.6", "requires": ["time"], "time": {"format": "%Y%m", "storage": "int"}}'

For a dataset named events, a query's time-range fragment still uses YYYY-MM-DD, regardless of the stored encoding. The start is inclusive and the end exclusive:

time_range:
  dimension: events.dt
  start: "2026-01-01"
  end: "2026-04-01"

Writing filters¶

Use time_range to filter D-FORMAT encoded fields. Its bounds always use YYYY-MM-DD; the engine converts them to the declared storage encoding.

where_sql rejects any reference to a D-FORMAT encoded field with unsupported_filter, even when the literal uses the correct encoding. This also applies to computed dataset fields, references inside functions or CAST, IS NULL, IN, BETWEEN, and comparisons between fields. Remove the reference from where_sql and use time_range instead. Conditions that cannot be expressed by time_range, such as selecting isolated dates or NULL values, are not supported through where_sql for these fields.

Native temporal declarations (date, datetime, timestamp, timestamp_tz) remain allowed in where_sql, as do fields without a D-FORMAT encoding.

This restriction applies to query where_sql. D-DERIVE filter and filters inside metric expressions still require literals in the stored encoding; they do not convert ISO date literals automatically. For example, a %Y%m%d string field requires '20260401', not '2026-04-01'. Use dosi list dimensions to inspect time_format and time_storage.

Configuration reference¶

The time object accepts format, storage, and granularity. Besides the native type names below, format accepts a continuous, ordered, fixed-width prefix of %Y%m%d%H%M%S. %Y is a four-digit year; %m, %d, %H, %M, and %S are two-digit, zero-padded month, day, hour (00–23), minute, and second. Stop after any component, but do not skip, repeat, or reorder them.

Any fixed literal text may appear between components or as a prefix/suffix: %Y-%m, %Y年%m月%d日, %Y.%m.%dT%H:%M, and %Y%m%d%H%M%S are valid. Write %% for a literal percent sign. Unknown directives, variable-width modifiers, fractional seconds, and timezone directives are not supported. For example, %Y%d, %d/%m/%Y, %-m, and %Y%m%d%z are rejected.

These combinations extend the existing 1.6 contract without a version bump. Engines predating this enhancement reject layouts they do not recognize:

custom_extensions:
  - vendor_name: DATUS
    data: '{"v":"1.6","requires":["time"],"time":{"format":"%Y年%m月%d日"}}'

Every stored value must follow the declared layout, including literal text and padding. Bare-column range comparisons require a column collation that orders these fixed-width values chronologically; use an appropriate binary/code-point ordering when necessary. The declaration does not inspect or change collation.

The original six encodings remain supported:

`format`	Stored value	Inferred grain	Allowed `storage`
`%Y%m%d`	`20260907`	`day`	`string`, `int`
`%Y-%m-%d`	`2026-09-07`	`day`	`string`
`%Y/%m/%d`	`2026/09/07`	`day`	`string`
`%Y%m`	`202609`	`month`	`string`, `int`
`%Y`	`2026`	`year`	`string`, `int`
`%Y-%m-%d %H:%M:%S`	`2026-09-07 12:30:00`	Not inferred	`string`

Native temporal declarations do not parse or re-encode the field:

`format`	Meaning	Inferred grain
`date`	Native date	`day`
`datetime`, `timestamp`, `timestamp_tz`	Native date and time	Not inferred

Prefer datatype: Date, DateTime, or DateTimeTz for new native temporal fields; no time.format is needed. The legacy spellings remain supported: date must agree with Date, datetime and timestamp with DateTime, and timestamp_tz with DateTimeTz. Native temporal datatypes reject stored patterns such as %Y%m%d. Time has no supported D-FORMAT encoding.

Native declarations do not accept storage. For encoded fields, an explicit storage must agree with datatype (String → "string", Integer → "int"); other declared datatypes cannot use patterns. Omitted storage is inferred from the datatype, or defaults to "string" when datatype is absent. "int" accepts only uninterrupted component prefixes without literal text (including literal digits), such as %Y%m%d%H or %Y%m%d%H%M%S. The longest encoding has 14 digits: choose a sufficiently wide integer-valued column, such as BIGINT or DECIMAL(n,0), for a compact timestamp. A physical DECIMAL(n,0) column holding an integer date key may declare logical Integer; logical Decimal is not an integer encoding. storage cannot be declared without format. These checks use the field expression's result type, not the source column type. Basic mode ignores the Datus block with a warning, while retaining the core datatype time-role default.

granularity accepts day, week, month, quarter, or year. It may be omitted when the format implies it. time.granularity and time_granularity are equally supported spellings: neither is deprecated, but they must agree when both are present. A declared grain cannot be finer than the encoding. A time object may declare only granularity, but cannot be empty. Year/month/day prefixes infer year/month/day regardless of their literal text; prefixes including a clock component do not infer a grain. Hours, minutes, and seconds in storage do not enable sub-day grouping or date-time time_range bounds: those query features remain unsupported. Parsed output uses January/day one for omitted calendar components and zero for omitted clock components; for example, %Y-%m denotes the first day of that month, and %Y%m%d%H denotes the start of that hour.

Grouping and range boundaries¶

A %Y%m field can be grouped by month, quarter, or year, but not by day or week: its stored values contain no such detail. Likewise, %Y supports year grouping. Requesting a finer grain produces grain_too_fine.

Range boundaries must align with the stored periods. For %Y%m, end: "2026-03-15" produces grain_too_fine: use "2026-03-01" to exclude March or "2026-04-01" to include it. The error offers both aligned bounds; the engine does not choose whether to include a partial month. Year encodings require year boundaries. These checks apply to the query's bounds; scan expansion for window calculations is handled by the engine.

Selected time dimensions are parsed into date/time values, including when no grain is requested. Do not rely on results retaining the stored text layout. D-FORMAT does not provide an output display-format setting.

Validation and known limits¶

The declaration must match the data. Dosi checks supported formats and configuration combinations, and — where the field declares core Field.datatype — that the declared storage and native format agree with it. It does not inspect warehouse column types or values, so dosi validate cannot establish that %Y%m%d matches the actual field expression's output.

A wrong format or storage type can cause a warehouse error, NULL groups, or incorrect filtering without an error. For example, declaring %Y%m%d for values stored as '2026-09-07' also generates filter literals in the wrong layout. Check both the expression's type and representative stored values.

ClickHouse uses parseDateTime, whose DateTime result is limited to roughly 1970-01-01 through 2106-02-07. Earlier or later dates, including an SCD2 '99991231' end-date sentinel, are not supported reliably for grouping or parsed projections. Verified on ClickHouse 24.8.14: parseDateTime rejects 1960, 2107, and 9999 dates with CANNOT_PARSE_DATETIME. parseDateTime64 is unavailable in that version. The BestEffort alternatives are not a safe replacement: parseDateTimeBestEffort clamps out-of-range values, and parseDateTime64BestEffort changes 9999-12-31 to 2299-12-31. Bare encoded time_range comparisons do not use this parser, but that does not make an out-of-range grouping safe.

In basic mode, DATUS extensions are ignored with a warning, including requires. Use Datus mode for fields that depend on encoded-date handling. Epoch encodings such as epoch_seconds and epoch_millis are not supported.

How it affects SQL¶

Grouping parses an encoded field into a date/time value and truncates it when a coarser grain is requested. At the stored grain, or with no requested grain, the GROUP BY key can remain the stored column and the SELECT expression parses it.

For time_range, the engine instead converts the bounds into the stored encoding. The examples above generate predicates of these forms:

WHERE dt >= '20260101' AND dt < '20260401'
WHERE etl_month >= 202601 AND etl_month < 202604

Keeping parsing functions off the filter column helps warehouses use partition pruning. Actual pruning depends on the warehouse, field expression, and partition definition.