Skip to content

Temporal functions

Constructors

XTQL SQL Return type

DATE 'date_str'

Construct a date - e.g. DATE '2020-01-01'

INTERVAL num 'unit'

INTERVAL interval 'unit TO unit'

Construct a single-field interval - e.g. INTERVAL 4 'HOUR'.

Construct a multi-field interval - e.g. INTERVAL '3 04:20:12.53' 'DAY TO SECOND'

(period start end)

PERIOD(start, end)

Constructs a period. Verifies that start <= end, or throws a runtime exception.

TIME 'time_str'

Construct a time - e.g. TIME '01:23:45.678'

TIMESTAMP 'timestamp_str'

Constructs a timestamp.

timestamp_str may be an ISO8601 or SQL timestamp representation, with or without time-zone. Examples:

  • TIMESTAMP '2020-01-01 12:34:56.789'

  • TIMESTAMP '2020-01-01T12:34:56.789'

  • TIMESTAMP '2020-01-01 12:34:56.789Z'

  • TIMESTAMP '2020-01-01T12:34:56.789+03:00'

  • TIMESTAMP '2020-01-01T12:34:56.789[Europe/London]

Temporal arithmetic

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

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

(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 struct with a :start and :end timestamp.

Most of these 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

(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

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

(extract "field" date-time)

EXTRACT('field', date_time)

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

(extract "field" date)

EXTRACT('field', date)

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

(extract "field" time)

EXTRACT('field', time)

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

(extract "field" interval)

EXTRACT('field', interval)

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