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:

d

day of month

D

day of year

M

month of year (7, 07, Jul, July)

y

year

Y

week based year

w

week of week based year (1-53)

e

day of week (2, Tue, Tuesday)

H

hour of day (0-23)

K

hour of day (0-11)

m

minute of hour

s

second of minute

S

fraction of second

a

am 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).

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:m:s

  • K:m:s a

  • H:m:s.SSS

  • K:m: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 Type

Remarks

<date> + <time>

timestamp

<date> + <interval>

timestamp

<date> + <int>

timestamp

int is interpreted as number of days (interval).

<time> + <date>

timestamp

<time> + <timestamp>

timestamp

<time> + <interval>

time

<timestamp> + <time>

timestamp

<timestamp> + <interval>

timestamp

<timestamp> + <int>

timestamp

int is interpreted as number of days

<interval> + <date>

timestamp

<interval> + <time>

time

<interval> + <timestamp>

timestamp

<interval> + <interval>

interval

Subtraction

The addition of temporals results in the following types:

Result Type

Remarks

<date> - <date>

interval

<date> - <time>

timestamp

<date> - <timestamp>

interval

<date> - <interval>

timestamp

<date> - <int>

timestamp

Int is interpreted as number of days (interval)

<time> - <time>

interval

<time> - <interval>

time

<timestamp> - <date>

interval

<timestamp> - <time>

timestamp

<timestamp> - <int>

timestamp

Int is interpreted as number of days

<timestamp> - <timestamp>

interval

<timestamp> - <interval>

timestamp

<interval> - <time>

interval

<interval> - <interval>

interval

Multiplication

Result Type

Remarks

<number> * <interval>

interval

Multiples interval by <number> times.

<number> * <time>

interval

Time is interpreted as interval, and multiple by <number> times.

For example:

2 * interval "1 day" // 2 days

Division

Result Type

Remarks

<interval> / <number>

interval

Divides interval by <number> times.

<time> / <number>

interval

Time is interpreted as interval, and divided by <number> times.

Negation

Result Type

Remarks

- <interval>

interval

Negative interval.

- <time>

interval

Time is interpreted as interval and negated.

TO_DATE

Parses a date from a string given a format.

Syntax:

TO_DATE(<string>, <string>)

For example:

TO_DATE("2018-02-01", "yyyy-MM-dd")

TRY_TO_DATE

Similar to TO_DATE but returns null instead of an error if the string does not match the template.

TO_TIME

Parses a time from a string given a format.

Syntax:

TO_TIME(<string>, <string>)

For example:

TO_TIME("11:00", "HH:mm")

TRY_TO_TIME

Similar to TO_TIME but returns null instead of an error if the string does not match the template.

TO_TIMESTAMP

Parses a timestamp from a string given a format.

Syntax:

TO_TIMESTAMP(<string>, <string>)

For example:

TO_TIMESTAMP("2018-02-01 01:02","yyyy-MM-dd H:m")

TRY_TO_TIMESTAMP

Similar to to_timestamp but returns null instead of an error if the string does not match the template.

UNIX_TIMESTAMP

Turns a timestamp into the corresponding Unix epoch (number of seconds since 01-01-1970) as a long. The reference time zone is the RAW time zone.

Syntax:

UNIX_TIMESTAMP(<timestamp>)

For example:

UNIX_TIMESTAMP(TIMESTAMP "2018-02-01 01:02")

YEAR

Extract the year component (as an integer) from a date or timestamp.

Syntax:

year(<date or timestamp>)

For example:

year(timestamp "2018-02-01 11:02:01") // 2018

MONTH

Extract the month component (as an integer) from a date or timestamp.

Syntax:

month(<date or timestamp>)

For example:

month(date "2018-02-01") // 2

DAY

Extract the respective component (as an integer) from a date or timestamp.

Syntax:

day(<date or timestamp>)

For example:

day(date "2018-02-01") // 1

HOUR

Extract the hour component (as an integer) from a time or timestamp.

Syntax:

hour(<time or timestamp>)

For example:

hour(time "21:02:01") // 21

MINUTE

Extract the minute component (as an integer) from a time or timestamp.

Syntax:

minute(<time or timestamp>)

For example:

minute(time "21:02:01") // 2

SECOND

Extract the second component (as an integer) from a time or timestamp.

Syntax:

second(<time or timestamp>)

For example:

second(time "21:02:01") // 1

MILLIS

Extract the milli-second component (as an integer) from a time or timestamp.

Syntax:

second(<time or timestamp>)

For example:

millis(time "21:02:01.003") // 3

MILLIS_TO_INTERVAL

Converts a integer number (int, byte, long etc.) to the corresponding interval in milliseconds

Syntax:

millis_to_interval(<long>)

For example:

millis_to_interval(100) // interval "100 millis"

INTERVAL_TO_MILLIS

Converts a interval to the corresponding number of milliseconds

Syntax:

interval_to_millis(<interval>)

For example:

interval_to_millis(interval "1 minute") // 60000
interval_to_millis(interval "1 day") // 86400000 (24*60*60*1000)

DATE_TRUNC

Truncates a timestamp or a date to the specified precision.

Syntax:

date_trunc(<string>, <timestamp or date>)

Valid values for precision are:

  • milliseconds

  • second

  • minute

  • hour

  • day

  • week

  • month

  • quarter

  • year

  • decade

  • century

  • millennium

For example:

date_trunc("hour", timestamp "2007-03-14 01:02:03.004") // "2007-03-14 01:00:00"
date_trunc("year", timestamp "2007-03-14 01:02:03.004") // "2007-01-01 00:00:00"

TIME_BUCKET

Similar to DATE_TRUNC but the truncating period can be any interval.

Syntax:

TIME_BUCKET(<interval>, <timestamp or date>)

For example:

TIME_BUCKET(interval "100 milli", timestamp "2007-03-14 01:02:03.248") // "2007-03-14 01:02:02.200"
TIME_BUCKET(interval "2 years", timestamp "2007-03-14 01:02:03.004") // "2006-01-01 00:00:00"

Warning

To avoid approximation errors the truncating interval cannot have years or months mixed with days, weeks, hours, etc.

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.

For 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"