Date/Time Types

A timezone-aware type representing date and time.

Casting is a simple way to obtain a datetime value in an expression:

SELECT <datetime>'2018-05-07T15:01:22.306916+00';
SELECT <datetime>'2018-05-07T15:01:22+00';

Note that when casting from strings, the string should be in ISO 8601 format with timezone included:

db> 

SELECT <datetime>'January 01 2019 UTC';

InvalidValueError: invalid input syntax for type
std::datetime: 'January 01 2019 UTC'
Hint: Please use ISO8601 format. Alternatively "to_datetime"
function provides custom formatting options.

db> 

SELECT <datetime>'2019-01-01T15:01:22';

InvalidValueError: invalid input syntax for type
std::datetime: '2019-01-01T15:01:22'
Hint: Please use ISO8601 format. Alternatively "to_datetime"
function provides custom formatting options.

See functions datetime_get(), to_datetime(), and to_str() for more ways of working with datetime.

A type representing date and time without time zone.

Casting is a simple way to obtain a cal::local_datetime value in an expression:

SELECT <cal::local_datetime>'2018-05-07T15:01:22.306916';
SELECT <cal::local_datetime>'2018-05-07T15:01:22';

Note that when casting from strings, the string should be in ISO 8601 format without timezone:

db> 

SELECT <cal::local_datetime>'2019-01-01T15:01:22+00';

InvalidValueError: invalid input syntax for type
cal::local_datetime: '2019-01-01T15:01:22+00'
Hint: Please use ISO8601 format. Alternatively
"cal::to_local_datetime" function provides custom formatting
options.

db> 

SELECT <cal::local_datetime>'January 01 2019';

InvalidValueError: invalid input syntax for type
cal::local_datetime: 'January 01 2019'
Hint: Please use ISO8601 format. Alternatively
"cal::to_local_datetime" function provides custom formatting
options.

See functions datetime_get(), cal::to_local_datetime(), and to_str() for more ways of working with cal::local_datetime.

A type representing a date without a time zone.

Casting is a simple way to obtain a cal::local_date value in an expression:

SELECT <cal::local_date>'2018-05-07';

Note that when casting from strings, the string should be in ISO 8601 date format.

See functions cal::date_get(), cal::to_local_date(), and to_str() for more ways of working with cal::local_date.

A type representing time without a time zone.

Casting is a simple way to obtain a cal::local_time value in an expression:

SELECT <cal::local_time>'15:01:22.306916';
SELECT <cal::local_time>'15:01:22';

Note that when casting from strings, the string should be in ISO 8601 time format.

See functions cal::time_get(), cal::to_local_time(), and to_str() for more ways of working with cal::local_time.

A type representing a span of time.

Valid units when converting from a string (and combinations of them): - 'microseconds' - 'milliseconds' - 'seconds' - 'minutes' - 'hours'

SELECT <duration>'45.6 seconds';
SELECT <duration>'15 milliseconds';
SELECT <duration>'48 hours 45 minutes';
SELECT <duration>'-7 minutes';

All date/time types support the + and - arithmetic operations with durations:

db> 

select <datetime>'2019-01-01T00:00:00Z' - <duration>'24 hours';

{<datetime>'2018-12-31T00:00:00+00:00'}

db> 

select <cal::local_time>'22:00' + <duration>'1 hour';

{<cal::local_time>'23:00:00'}

Duration is a fixed number of seconds and microseconds and isn’t adjusted by timezone, length of month or anything else in datetime calculations.

See functions to_duration(), and to_str() and date/time operators for more ways of working with duration.

A type representing a span of time.

Unlike std::duration a relative_duration is not a precise measurment because it uses 3 different units under the hood: months, days and seconds. However not all months have the same number of days and not all days have the same number of seconds. For example 2019 was a leap year and had 366 days. Notice how the number of hours in each year below is different.

db> 
... 
... 
... 
... 

WITH
    first_day_of_2020 := <datetime>'2020-01-01T00:00:00Z',
    one_year := <cal::relative_duration>'1 year',
    first_day_of_next_year := first_day_of_2020 + one_year
SELECT first_day_of_next_year - first_day_of_2020;

{<duration>'8784:00:00'}

db> 
... 
... 
... 
... 

WITH
    first_day_of_2019 := <datetime>'2019-01-01T00:00:00Z',
    one_year := <cal::relative_duration>'1 year',
    first_day_of_next_year := first_day_of_2019 + one_year
SELECT first_day_of_next_year - first_day_of_2019;

{<duration>'8760:00:00'}

Valid units when converting from a string (and combinations of them): - 'microseconds' - 'milliseconds' - 'seconds' - 'minutes' - 'hours' - 'days' - 'weeks' - 'months' - 'years' - 'decades' - 'centuries' - 'millennium'

SELECT <cal::relative_duration>'45.6 seconds';
SELECT <cal::relative_duration>'15 milliseconds';
SELECT <cal::relative_duration>'3 weeks 45 minutes';
SELECT <cal::relative_duration>'-7 millennium';

All date/time types support the + and - arithmetic operations with relative_durations:

db> 
... 

select <datetime>'2019-01-01T00:00:00Z' -
       <cal::relative_duration>'3 years';

{<datetime>'2016-01-01T00:00:00+00:00'}

db> 

select <cal::local_time>'22:00' + <cal::relative_duration>'1 hour';

{<cal::local_time>'23:00:00'}

See functions cal::to_relative_duration(), and to_str() and date/time operators for more ways of working with cal::relative_duration.