Skip to main content

Temporals

The temporal types include date, time, timestamp and interval.

Note

Currently, the temporal types do not support time zones.

Construction

Temporals can be constructed from strings, either using the date, time and timestamp keywords which apply implicit parsing templates to the string (see below) or to_date, to_time and to_timestamp primitives which take the template string parameter to be applied.

Templates

The following patterns are supported for temporal parsing:

dday of month
Dday of year
Mmonth of year (7, 07, Jul, July)
yyear
Yweek based year, see java doc for more info.
wweek of week based year (1-53)
eday of week (2, Tue, Tuesday)
Hhour of day (0-23)
Khour of day (0-11)
mminute of hour
ssecond of minute
Sfraction of second
aam or pm
'escape for text (‘foo’, ‘bar’)

When repeated, these characters indicate a minimum number of characters the string should match.

  • MMM parses short month names (Jan, Feb) and MMMM parses full month names (January, February).
  • yy parses as the last two digits of a year with a base year of 2000 (18 parses as 2018, 95 parses as 2095).
  • e parses day of week (1-7), eee parses short week day names (Mon, Tue) and eeee parses full week day names (Monday, Tuesday).
Note

Raw follows java conventions for date templates see java documentation for more info.

Examples:

  • ISO 8601 date: to_date("2018-02-01", "yyyy-MM-dd")
  • ISO 8601 ordinal date: to_date("2019-062", "yyyy-DDD")
  • ISO 8601 week date: to_date("2019-W01-1", "YYYY-'W'ww-e")
  • ISO 8601 timestamp: to_timestamp("2018-02-01T01:02:03.004","yyyy-MM-dd'T'H:m:s.SSS")
  • RFC 1123 timestamp: to_timestamp("Wed, 09 Dec 2015 18:59:42", "eee, dd MMM yyyy H:m:s")

date

Example: date "2018-02-01"

Fails with type error if constant doesn’t match any known template.

The templates attempted for date are (in order):

  • d-M-yyyy
  • d/M/yyyy
  • d-M-yy
  • d/M/yy
  • yyyy-M-d
  • yyyy/M/d
  • yy-M-d
  • yy/M/d
  • yyMMdd
  • yyyyMMdd
  • MMMM d yyyy
  • MMMM d yy
  • MMM-d-yyyy
  • MMM-d-yy
  • MMM d yyyy
  • MMM d yy
  • d MMMM yyyy
  • d MMMM yy
  • d MMM yyyy
  • d-MMM-yyyy
  • d-MMM-yy
  • yyyy MMMM d
  • yy MMMM d
  • yyyy-MMM-d
  • yy-MMM-d
  • yyyy MMM d
  • yy MMM d
  • yyyy d MMMM
  • yy d MMMM
  • yyyy-d-MMM
  • yy-d-MMM
  • yyyy d MMM
  • yy d MMM

time

Example: time "23:54:12"

Fails with type error if constant doesn’t match any known template. The templates attempted for time are (in order):

  • H:m
  • K:m a
  • HⓂ️s
  • KⓂ️s a
  • HⓂ️s.SSS
  • KⓂ️s.SSS a
  • HHmmss

timestamp

Example: timestamp "2018-02-01 01:02"

The constants accepted for timestamp are a combination of all date and time string constants in the order defined above.

interval

Example: interval "1 year 2 months"

The constants accepted for interval are a combination of the following constructs:

  • HH:mm:ss.SSS
  • HH:mm:ss
  • HH:mm
  • <int> years
  • <int> months
  • <int> days
  • <int> weeks
  • <int> hours
  • <int> minutes
  • <int> seconds
  • <int> mills

Addition

The addition of temporals results in the following types:

Result TypeRemarks
<date> + <time>timestamp
<date> + <interval>timestamp
<date> + <int>timestampint is interpreted as number of days (interval).
<time> + <date>timestamp
<time> + <timestamp>timestamp
<time> + <interval>time
<timestamp> + <time>timestamp
<timestamp> + <interval>timestampint is interpreted as number of days
<interval> + <date>timestamp
<interval> + <time>time
<interval> + <timestamp>timestamp
<interval> + <interval>interval

Subtraction

The subtraction of temporals results in the following types:

Result TypeRemarks
<date> - <time>timestamp
<date> - <interval>timestamp
<date> - <int>timestampint is interpreted as number of days (interval).
<time> - <date>timestamp
<time> - <timestamp>timestamp
<time> - <interval>time
<timestamp> - <time>timestamp
<timestamp> - <interval>timestampint is interpreted as number of days
<interval> - <date>timestamp
<interval> - <time>time
<interval> - <timestamp>timestamp
<interval> - <interval>interval

Multiplication

Result TypeRemarks
<number> * <interval>intervalMultiples interval by <number> times.
<number> * <time>intervalTime is interpreted as interval, and multiple by <number> times.
Example
2 * interval "1 day" // 2 days

Division

Result TypeRemarks
<interval> / <number>intervalDivides interval by <number> times.
<time> / <number>intervalTime is interpreted as interval, and divided by <number> times.

Negation

Result TypeRemarks
- <interval>intervalNegative interval.
- <time>intervalTime is interpreted as interval and negated.

Operations

Comparisons

All temporal types can be compared (equality and inequality) with own type.

In addition, interval and time can be compared with each other, where time is converted to interval before comparison.

Intervals are first converted to milli-seconds before comparison:

  • days are equivalent to 24h.
  • each year is equivalent to 365.25 days and the total value is rounded down.
  • each month is equivalent to 30.4375 days (365.25 / 12) and the total value is rounded down.

Casts

All temporal types can be casted to string.

Timestamps can be casted to:

  • date to extract the date component of the timestamp;
  • time to extract the time component of the timestamp.
Example
cast(timestamp "2018-01-02 09:04:00" as date) // date "2018-01-02"
cast(timestamp "2018-01-02 09:04:00" as time) // time "09:04:00"