Temporal functions

Note	For information on what temporal types we support and how to construct temporal literals, see temporal types.

Temporal arithmetic

XTQL SQL Return type

XTQL	SQL	Return type
`(+ date-time duration)` `(+ duration date-time)` `(+ date-time interval)` `(+ interval date-time)`	`date_time + duration` `duration + date_time` `date_time + interval` `interval + date_time`	date-time
`(+ duration duration)`	`duration + duration`	duration
`(+ interval interval)`	`interval + interval`	interval
`(- date-time duration)` `(- date-time interval)`	`date_time - duration` `date_time - interval`	date-time
`(- duration duration)`	`duration - duration`	duration
`(- interval interval)`	`interval - interval`	interval
`(- date date)`	`date - date`	integer (number of days elapsed)
`(* duration num)` `(* num duration)`	`duration * num` `num * duration`	duration
`(* interval num)` `(* num interval)`	`interval * num` `num * interval`	interval
`(/ duration num)`	`duration / num`	duration
`(/ interval num)`	`interval / num`	interval
`(abs duration)`	`ABS(duration)`	duration
`(abs interval)`	`ABS(interval)`	interval

(+ date-time duration)

(+ duration date-time)

(+ date-time interval)

(+ interval date-time)

date_time + duration

duration + date_time

date_time + interval

interval + date_time

date-time

(+ duration duration)

duration + duration

duration

(+ interval interval)

interval + interval

interval

(- date-time duration)

(- date-time interval)

date_time - duration

date_time - interval

date-time

(- duration duration)

duration - duration

duration

(- interval interval)

interval - interval

interval

(- date date)

date - date

integer (number of days elapsed)

(* duration num)

(* num duration)

duration * num

num * duration

duration

(* interval num)

(* num interval)

interval * num

num * interval

interval

(/ duration num)

duration / num

duration

(/ interval num)

interval / num

interval

(abs duration)

ABS(duration)

duration

(abs interval)

ABS(interval)

interval

Note

Date-times are first cast to comparable resolutions before performing arithmetic. e.g. adding a date-time with second resolution to a duration with microsecond resolution will first cast the date-time to microsecond resolution.
If local and TZ-aware date-times are passed to the same operation, the local date-time is first converted to a TZ-aware date-time using the query’s time zone.
If any part of any operation would cause an overflow (including implicit casts), a runtime exception will be raised.

Current time

XTDB allows fine-grained control over user requests for the 'current time', to allow for fully repeatable queries.

The wall-clock time of a query is fixed when the query starts. It can be explicitly specified by passing :current-time to the query options; otherwise, it will snapshot the current-time of the XTDB node.
The wall-clock time of a query within a transaction is fixed to the system-time of the transaction, as recorded by the transaction log (or overridden using the :system-time option to submit-tx).
Each transaction/query has a default time-zone, which defaults to the time-zone of the XTDB node’s JVM unless overridden in the query options (default-tz).

XTQL SQL

XTQL	SQL
`(current-timestamp <precision>?)`	`CURRENT_TIMESTAMP` `CURRENT_TIMESTAMP(precision)`	Returns the current wall-clock date/time as a timestamp with time-zone.
`(current-date <precision>?)`	`CURRENT_DATE` `CURRENT_DATE(precision)`	Returns the current UTC wall-clock date.
`(current-time <precision>?)`	`CURRENT_TIME` `CURRENT_TIME(precision)`	Returns the current UTC wall-clock time.
`(local-timestamp <precision>?)`	`LOCAL_TIMESTAMP` `LOCAL_TIMESTAMP(precision)`	Returns the current wall-clock date/time as a local timestamp (without time-zone), as in the query’s time-zone.
`(current-timestamp <precision>?)`	`LOCAL_TIME` `LOCAL_TIME(precision)`	Returns the current wall-clock time as a local time (without time-zone), as in the query’s time-zone.

(current-timestamp <precision>?)

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP(precision)

Returns the current wall-clock date/time as a timestamp with time-zone.

(current-date <precision>?)

CURRENT_DATE

CURRENT_DATE(precision)

Returns the current UTC wall-clock date.

(current-time <precision>?)

CURRENT_TIME

CURRENT_TIME(precision)

Returns the current UTC wall-clock time.

(local-timestamp <precision>?)

LOCAL_TIMESTAMP

LOCAL_TIMESTAMP(precision)

Returns the current wall-clock date/time as a local timestamp (without time-zone), as in the query’s time-zone.

(current-timestamp <precision>?)

LOCAL_TIME

LOCAL_TIME(precision)

Returns the current wall-clock time as a local time (without time-zone), as in the query’s time-zone.

Periods

Periods in XTDB are represented as a pair of timestamps with inclusive start and exclusive end ('closed-open'). They are constructed with the PERIOD function:

XTQL SQL

XTQL	SQL
`(period from to)`	`PERIOD(from, to)`	Returns a new period from `from` to `to`. `from` must be strictly less than `to`.

(period from to)

PERIOD(from, to)

Returns a new period from from to to.

from must be strictly less than to.

Most of the below period comparators have 'strictly' and 'immediate' variants.

'strictly' variants check that the two periods don’t meet - e.g. precedes? will return true if the earlier period ends at the same time the second period starts; strictly-precedes? will return false.
'immediately' variants check that the two periods do meet.

These functions will return null if any of their arguments are null.

XTQL SQL

XTQL	SQL
`(contains? p1 p2)` `(strictly-contains? p1 p2)`	`p1 CONTAINS p2` `p1 STRICTLY CONTAINS p2`	Returns true iff `p1` starts before `p2` starts and ends after `p2` ends. `contains?`: `p1-start <= p2-start`, `p1-end >= p2-end` `strictly-contains?`: `p1-start < p2-start`, `p1-end > p2-end`
`(equals? p1 p2)`	`p1 EQUALS p2`	Returns true iff the two periods are equal `equals?`: `p1-start = p2-start`, `p1-end = p2-end`
`(lags? p1 p2)` `(strictly-lags? p1 p2)` `(immediately-lags? p1 p2)`	`p1 LAGS p2` `p1 STRICTLY LAGS p2` `p1 IMMEDIATELY LAGS p2`	Returns true iff `p1` starts after `p2` starts and ends after `p2` ends. `lags?`: `p1-start >= p2-start`, `p1-end > p2-end` `strictly-lags?`: `p1-start > p2-start`, `p1-end > p2-end` `immediately-lags?`: `p1-start = p2-start`, `p1-end > p2-end`
`(leads? p1 p2)` `(strictly-leads? p1 p2)` `(immediately-leads? p1 p2)`	`p1 LEADS p2` `p1 STRICTLY LEADS p2` `p1 IMMEDIATELY LEADS p2`	Returns true iff `p1` starts before `p2` starts and ends before `p2` ends. `leads?`: `p1-start < p2-start`, `p1-end <= p2-end` `strictly-leads?`: `p1-start < p2-start`, `p1-end < p2-end` `immediately-leads?`: `p1-start < p2-start`, `p1-end = p2-end`
`(overlaps? p1 p2)`	`p1 OVERLAPS p2`	Returns true iff `p1` starts before `p2` ends and ends after `p2` starts `overlaps?`: `p1-start < p2-end`, `p1-end > p2-start` `strictly-overlaps?`: `p1-start > p2-start`, `p1-end < p2-end`
`(precedes? p1 p2)` `(strictly-precedes? p1 p2)` `(immediately-precedes? p1 p2)`	`p1 PRECEDES p2` `p1 STRICTLY PRECEDES p2` `p1 IMMEDIATELY PRECEDES p2`	Returns true iff `p1` ends before `p2` starts `precedes?`: `p1-end <= p2-start` `strictly-precedes?`: `p1-end < p2-start` `immediately-precedes?`: `p1-end = p2-start`
`(succeeds? p1 p2)` `(strictly-succeeds? p1 p2)` `(immediately-succeeds? p1 p2)`	`p1 SUCCEEDS p2` `p1 STRICTLY SUCCEEDS p2` `p1 IMMEDIATELY SUCCEEDS p2`	Returns true iff `p1` starts after `p2` ends `succeeds?`: `p1-start >= p2-end` `strictly-succeeds?`: `p1-start > p2-end` `immediately-succeeds?`: `p1-start = p2-end`

(contains? p1 p2)

(strictly-contains? p1 p2)

p1 CONTAINS p2

p1 STRICTLY CONTAINS p2

Returns true iff p1 starts before p2 starts and ends after p2 ends.

contains?: p1-start <= p2-start, p1-end >= p2-end
strictly-contains?: p1-start < p2-start, p1-end > p2-end

(equals? p1 p2)

p1 EQUALS p2

Returns true iff the two periods are equal

equals?: p1-start = p2-start, p1-end = p2-end

(lags? p1 p2)

(strictly-lags? p1 p2)

(immediately-lags? p1 p2)

p1 LAGS p2

p1 STRICTLY LAGS p2

p1 IMMEDIATELY LAGS p2

Returns true iff p1 starts after p2 starts and ends after p2 ends.

lags?: p1-start >= p2-start, p1-end > p2-end
strictly-lags?: p1-start > p2-start, p1-end > p2-end
immediately-lags?: p1-start = p2-start, p1-end > p2-end

(leads? p1 p2)

(strictly-leads? p1 p2)

(immediately-leads? p1 p2)

p1 LEADS p2

p1 STRICTLY LEADS p2

p1 IMMEDIATELY LEADS p2

Returns true iff p1 starts before p2 starts and ends before p2 ends.

leads?: p1-start < p2-start, p1-end <= p2-end
strictly-leads?: p1-start < p2-start, p1-end < p2-end
immediately-leads?: p1-start < p2-start, p1-end = p2-end

(overlaps? p1 p2)

p1 OVERLAPS p2

Returns true iff p1 starts before p2 ends and ends after p2 starts

overlaps?: p1-start < p2-end, p1-end > p2-start
strictly-overlaps?: p1-start > p2-start, p1-end < p2-end

(precedes? p1 p2)

(strictly-precedes? p1 p2)

(immediately-precedes? p1 p2)

p1 PRECEDES p2

p1 STRICTLY PRECEDES p2

p1 IMMEDIATELY PRECEDES p2

Returns true iff p1 ends before p2 starts

precedes?: p1-end <= p2-start
strictly-precedes?: p1-end < p2-start
immediately-precedes?: p1-end = p2-start

(succeeds? p1 p2)

(strictly-succeeds? p1 p2)

(immediately-succeeds? p1 p2)

p1 SUCCEEDS p2

p1 STRICTLY SUCCEEDS p2

p1 IMMEDIATELY SUCCEEDS p2

Returns true iff p1 starts after p2 ends

succeeds?: p1-start >= p2-end
strictly-succeeds?: p1-start > p2-end
immediately-succeeds?: p1-start = p2-end

The below functions operate on periods:

XTQL SQL

XTQL	SQL
`(lower p)`	`LOWER(p)`	Returns the lower bound of the provided period, or null if it infinite.
`(lower-inf p)`	`LOWER_INF(p)`	Returns true iff the lower bound of the provided period is infinite.
`(upper p)`	`UPPER(p)`	Returns the upper bound of the provided period, or null if it is infinite.
`(upper-inf p)`	`UPPER_INF(p)`	Returns true iff the upper bound of the provided period is infinite.
`(* p1 p2)`	`p1 * p2`	Returns the intersection of the two periods. e.g.: if you have periods for `[2020, 2022]` and `[2021, 2023]`, the intersection is `[2021, 2022]` if the periods do not intersect (including if they 'meet' - `[2020, 2022]` and `[2022, 2024]`), this function will return null.

(lower p)

LOWER(p)

Returns the lower bound of the provided period, or null if it infinite.

(lower-inf p)

LOWER_INF(p)

Returns true iff the lower bound of the provided period is infinite.

(upper p)

UPPER(p)

Returns the upper bound of the provided period, or null if it is infinite.

(upper-inf p)

UPPER_INF(p)

Returns true iff the upper bound of the provided period is infinite.

(* p1 p2)

p1 * p2

Returns the intersection of the two periods. e.g.:

if you have periods for [2020, 2022] and [2021, 2023], the intersection is [2021, 2022]
if the periods do not intersect (including if they 'meet' - [2020, 2022] and [2022, 2024]), this function will return null.

Miscellaneous

XTQL

SQL

(date-trunc "unit" date-time)

DATE_TRUNC(unit, date_time)

Truncates the date-time to the given time-unit, which must be one of MILLENNIUM, CENTURY, DECADE, YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND or MICROSECOND

(date-trunc "unit" date-time time-zone)

DATE_TRUNC(unit, date_time, 'time_zone')

Truncates a timezone aware date-time to the given time-unit, which must be one of MILLENNIUM, CENTURY, DECADE, YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND or MICROSECOND, and then converts it to the specified time-zone. The specified time-zone must be a valid time-zone identifier (see here)

(date-trunc "unit" interval)

DATE_TRUNC(unit, interval)

Truncates the given interval to the given time-unit, which must be one of MILLENNIUM, CENTURY, DECADE, YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND or MICROSECOND

DATE_BIN(stride, timestamp [, origin])

Bins the given timestamp within the given 'stride' interval, optionally relative to the given origin (or '1970-01-01' if not supplied).

e.g. TIMESTAMP '2024-01-01T12:34:00Z' with an INTERVAL 'PT20M' stride would yield 2024-01-01T12:20Z.

RANGE_BINS(stride, period [, origin])

Aligns the given period within bins of the given 'stride' interval, optionally relative to the given origin (or '1970-01-01' if not supplied).

Returns an array of structs, each containing the _from and _to of the bin, and a _weight representing the proportion of the original range contained within the given bin.

e.g.

A period of 00:05-00:10 within 15 minute bins yields one bin, 00:00-00:15 with weight 1.0:

RANGE_BINS(INTERVAL 'PT15M', PERIOD(TIMESTAMP '2020-01-01T00:05Z', TIMESTAMP '2020-01-01T00:10Z'))

→ [{_from: '2020-01-01T00:00Z', _to: '2020-01-01T00:15Z', _weight: 1.0}]
A period of 12:57-13:02 within hourly bins yields two bins, 12:00-13:00 with weight 0.6, and 13:00-14:00 with weight 0.4:

RANGE_BINS(INTERVAL 'PT1H', PERIOD(TIMESTAMP '2020-01-01T12:57Z', TIMESTAMP '2020-01-01T13:02Z'))

→ [{_from: '2020-01-01T12:00Z', _to: '2020-01-01T13:00Z', _weight: 0.6}, {_from: '2020-01-01T13:00Z', _to: '2020-01-01T14:00Z', _weight: 0.4}]

(extract "field" date-time)

EXTRACT(field FROM date_time)

Extracts the given field from the date-time. Field must be one of YEAR, MONTH, DAY, HOUR, MINUTE or SECOND. Datetimes with timezones additionally support field values of TIMEZONE_HOUR and TIMEZONE_MINUTE.

(extract "field" date)

EXTRACT(field FROM date)

Extracts the given field from the date. Field must be one of YEAR, MONTH or DAY.

(extract "field" time)

EXTRACT(field FROM time)

Extracts the given field from the time. Field must be one of HOUR, MINUTE or SECOND.

(extract "field" interval)

EXTRACT(field FROM interval)

Extracts the given field from the interval. Field must be one of YEAR, MONTH, DAY, HOUR, MINUTE or SECOND.

(age date-time date-time)

AGE(date_time, date_time)

Returns an interval representing the difference between two date-times - subtracting the second value from the first. Works for any combination of date times, date times with time zone identifiers, or dates.