Date/Time Functions and Operators

dt + dt

Time interval addition.

dt - dt

Time interval and date/time subtraction.

dt = dt, dt < dt, …

Comparison operators.

to_str()

Render a date/time value to a string.

to_datetime()

Create a datetime value.

to_local_datetime()

Create a local_datetime value.

to_local_date()

Create a local_date value.

to_local_time()

Create a local_time value.

to_duration()

Create a duration value.

datetime_get()

Extract a specific element of input datetime by name.

time_get()

Extract a specific element of input time by name.

date_get()

Extract a specific element of input date by name.

duration_get()

Extract a specific element of input duration by name.

datetime_trunc()

Truncate the input datetime to a particular precision.

duration_trunc()

Truncate the input duration to a particular precision.

datetime_current()

Return the current server date and time.

datetime_of_transaction()

Return the date and time of the start of the current transaction.

datetime_of_statement()

Return the date and time of the start of the current statement.

operator datetime + duration -> datetimelocal_datetime + duration -> local_datetimelocal_date + duration -> local_datelocal_time + duration -> local_timeduration + duration -> duration

Time interval addition.

This operator is commutative.

db> 
SELECT <local_time>'22:00' + <duration>'1 hour';
{<local_time>'23:00:00'}
db> 
SELECT <duration>'1 hour' + <local_time>'22:00';
{<local_time>'23:00:00'}
db> 
SELECT <duration>'1 hour' + <duration>'2 hours';
{<duration>'3:00:00'}
operator duration - duration -> durationdatetime - datetime -> durationlocal_datetime - local_datetime -> durationlocal_time - local_time -> durationlocal_date - local_date -> durationdatetime - duration -> datetimelocal_datetime - duration -> local_datetimelocal_time - duration -> local_timelocal_date - duration -> local_date

Time interval and date/time subtraction.

db> 
... 
SELECT <datetime>'January 01 2019 UTC' -
  <duration>'1 day';
{<datetime>'2018-12-31T00:00:00+00:00'}
db> 
... 
SELECT <datetime>'January 01 2019 UTC' -
  <datetime>'January 02 2019 UTC';
{<duration>'-1 day, 0:00:00'}
db> 
... 
SELECT <duration>'1 hour' -
  <duration>'2 hours';
{<duration>'-1 day, 23:00:00'}

It is an error to subtract a date/time object from a time interval:

db> 
... 
SELECT <duration>'1 day' -
  <datetime>'January 01 2019 UTC';
QueryError: operator '-' cannot be applied to operands ...

It is also an error to subtract timezone-aware std::datetime to or from std::local_datetime:

db> 
... 
SELECT <datetime>'January 01 2019 UTC' -
  <local_datetime>'January 02 2019';
QueryError: operator '-' cannot be applied to operands ...
function std::datetime_current() -> datetime

Return the current server date and time.

db> 
SELECT datetime_current();
{'2018-05-14T20:07:11.755827+00:00'}
function std::datetime_of_transaction() -> datetime

Return the date and time of the start of the current transaction.

function std::datetime_of_statement() -> datetime

Return the date and time of the start of the current statement.

function std::datetime_get(dt: datetime, el: str) -> float64std::datetime_get(dt: local_datetime, el: str) -> float64

Extract a specific element of input datetime by name.

The datetime scalar has the following elements available for extraction:

  • 'epoch' - the number of seconds since 1970-01-01 00:00:00 UTC for datetime or local time for local_datetime. It can be negative.

  • 'century' - the century according to the Gregorian calendar

  • 'day' - the day of the month (1-31)

  • 'decade' - the decade (year divided by 10 and rounded down)

  • 'dow' - the day of the week from Sunday (0) to Saturday (6)

  • 'doy' - the day of the year (1-366)

  • 'hour' - the hour (0-23)

  • 'isodow' - the ISO day of the week from Monday (1) to Sunday (7)

  • 'isoyear' - the ISO 8601 week-numbering year that the date falls in. See the 'week' element for more details.

  • 'microseconds' - the seconds including fractional value expressed as microseconds

  • 'millennium' - the millennium. The third millennium started on Jan 1, 2001.

  • 'milliseconds' - the seconds including fractional value expressed as milliseconds

  • 'minute' - the minutes (0-59)

  • 'month' - the month of the year (1-12)

  • 'quarter' - the quarter of the year (1-4)

  • 'second' - the seconds, including fractional value from 0 up to and not including 60

  • 'week' - the number of the ISO 8601 week-numbering week of the year. ISO weeks are defined to start on Mondays and the first week of a year must contain Jan 4 of that year.

  • 'year' - the year

db> 
... 
... 
SELECT datetime_get(
    <datetime>'2018-05-07T15:01:22.306916+00',
    'epoch');
{1525705282.306916}
db> 
... 
... 
SELECT datetime_get(
    <datetime>'2018-05-07T15:01:22.306916+00',
    'year');
{2018}
db> 
... 
... 
SELECT datetime_get(
    <datetime>'2018-05-07T15:01:22.306916+00',
    'quarter');
{2}
db> 
... 
... 
SELECT datetime_get(
    <datetime>'2018-05-07T15:01:22.306916+00',
    'doy');
{127}
db> 
... 
... 
SELECT datetime_get(
    <datetime>'2018-05-07T15:01:22.306916+00',
    'hour');
{15}
function std::time_get(dt: local_time, el: str) -> float64

Extract a specific element of input time by name.

The local_time scalar has the following elements available for extraction:

  • 'epoch'

  • 'hour'

  • 'microseconds'

  • 'milliseconds'

  • 'minute'

  • 'second'

For full description of what these elements extract see datetime_get().

db> 
... 
SELECT time_get(
    <local_time>'15:01:22.306916', 'minute');
{1}
db> 
... 
SELECT time_get(
    <local_time>'15:01:22.306916', 'milliseconds');
{22306.916}
function std::date_get(dt: local_date, el: str) -> float64

Extract a specific element of input date by name.

Valid elements for local_date are the same as for local_datetime in datetime_get().

db> 
... 
... 
SELECT date_get(
    <local_date>'2018-05-07T15:01:22.306916',
    'century');
{21}
db> 
... 
... 
SELECT date_get(
    <local_date>'2018-05-07T15:01:22.306916',
    'year');
{2018}
db> 
... 
... 
SELECT date_get(
    <local_date>'2018-05-07T15:01:22.306916',
    'month');
{5}
db> 
... 
... 
SELECT date_get(
    <local_date>'2018-05-07T15:01:22.306916',
    'doy');
{127}
function std::duration_get(dt: duration, el: str) -> float64

Extract a specific element of input duration by name.

The duration scalar has the following elements available for extraction:

  • 'century' - the number of centuries, rounded towards 0

  • 'day' - the number of days

  • 'decade' - the number of decades, rounded towards 0

  • 'epoch' - the total number of seconds in the duration

  • 'hour' - the hour (0-23)

  • 'microseconds' - the seconds including fractional value expressed as microseconds

  • 'millennium' - the number of millennia, rounded towards 0

  • 'milliseconds' - the seconds including fractional value expressed as milliseconds

  • 'minute' - the minutes (0-59)

  • 'month' - the number of months, modulo 12 (0-11)

  • 'quarter' - the quarter of the year (1-4), based on months

  • 'second' - the seconds, including fractional value from 0 up to and not including 60

  • 'year' - the number of years

Due to inherent ambiguity of counting days, months, and years the duration does not attempt to automatically convert between them. So <duration>'24 hours' is not necessarily the same as <duration>'1 day'. So one must be careful when adding or subtracting duration values.

db> 
SELECT duration_get(<duration>'24 hours', 'day');
{0}
db> 
SELECT duration_get(<duration>'24 hours', 'hour');
{24}
db> 
SELECT duration_get(<duration>'1 day', 'day');
{1}
db> 
SELECT duration_get(<duration>'1 day', 'hour');
{0}
db> 
... 
SELECT duration_get(
    <duration>'24 hours' - <duration>'1 day', 'hour');
{24}
db> 
... 
SELECT duration_get(
    <duration>'24 hours' - <duration>'1 day', 'day');
{-1}

However, 'epoch' calculations assume that 1 day = 24 hours, 1 month = 30 days and 1 year = 365.25 days or 12 months (depending on what is being converted).

db> 
... 
SELECT duration_get(
    <duration>'24 hours' - <duration>'1d', 'epoch');
{0}
db> 
SELECT duration_get(<duration>'1 year', 'epoch');
{31557600}
db> 
SELECT duration_get(<duration>'365.25 days', 'epoch');
{31557600}
db> 
... 
SELECT duration_get(
    <duration>'365 days 6 hours', 'epoch');
{31557600}
function std::datetime_trunc(dt: datetime, unit: str) -> datetime

Truncate the input datetime to a particular precision.

The valid unit values in order or decreasing precision are:

  • 'microseconds'

  • 'milliseconds'

  • 'second'

  • 'minute'

  • 'hour'

  • 'day'

  • 'week'

  • 'month'

  • 'quarter'

  • 'year'

  • 'decade'

  • 'century'

  • 'millennium'

db> 
... 
SELECT datetime_trunc(
    <datetime>'2018-05-07T15:01:22.306916+00', 'year');
{'2018-01-01T00:00:00+00:00'}
db> 
... 
SELECT datetime_trunc(
    <datetime>'2018-05-07T15:01:22.306916+00', 'quarter');
{'2018-04-01T00:00:00+00:00'}
db> 
... 
SELECT datetime_trunc(
    <datetime>'2018-05-07T15:01:22.306916+00', 'day');
{'2018-05-07T00:00:00+00:00'}
db> 
... 
SELECT datetime_trunc(
    <datetime>'2018-05-07T15:01:22.306916+00', 'hour');
{'2018-05-07T15:00:00+00:00'}
function std::duration_trunc(dt: duration, unit: str) -> duration

Truncate the input duration to a particular precision.

The valid unit values are the same as for datetime_trunc().

db> 
... 
SELECT duration_trunc(
    <duration>'3 days 15:01:22', 'day');
{'3 days'}
db> 
... 
SELECT duration_trunc(
    <duration>'15:01:22.306916', 'minute');
{'15:01:00'}

The usual caveat that duration doesn’t automatically convert units applies to how truncation works.

function std::to_datetime(s: str, fmt: OPTIONAL str = {}) -> datetimestd::to_datetime(local: local_datetime, zone: str) -> datetimestd::to_datetime(year: int64, month: int64, day: int64, hour: int64, min: int64, sec: float64, timezone: str) -> datetime

Create a datetime value.

The datetime value can be parsed from the input str s. By default, the input is expected to conform to ISO 8601 format. However, the optional argument fmt can be used to override the input format to other forms.

db> 
SELECT to_datetime('2018-05-07T15:01:22.306916+00');
{<datetime>'2018-05-07T15:01:22.306916+00:00'}
db> 
SELECT to_datetime('2018-05-07T15:01:22+00');
{<datetime>'2018-05-07T15:01:22+00:00'}
db> 
... 
SELECT to_datetime('May 7th, 2018 15:01:22 +00',
                   'Mon DDth, YYYY HH24:MI:SS TZM');
{<datetime>'2018-05-07T15:01:22+00:00'}

Alternatively, the datetime value can be constructed from a std::local_datetime value:

db> 
... 
SELECT to_datetime(
  <local_datetime>'January 1, 2019 12:00AM', 'HKT');
{<datetime>'2018-12-31T16:00:00+00:00'}

Yet another way to construct a the datetime value is to specify it in terms of its component parts: year, month, day, hour, min, sec, and timezone

db> 
... 
SELECT to_datetime(
    2018, 5, 7, 15, 1, 22.306916, 'UTC');
{<datetime>'2018-05-07T15:01:22.306916+00:00'}
function std::to_local_datetime(s: str, fmt: OPTIONAL str = {}) -> local_datetimestd::to_local_datetime(dt: datetime, zone: str) -> local_datetimestd::to_local_datetime(year: int64, month: int64, day: int64, hour: int64, min: int64, sec: float64) -> local_datetime

Create a local_datetime value.

Similar to to_datetime(), the local_datetime value can be parsed from the input str s with an optional fmt argument or it can be given in terms of its component parts: year, month, day, hour, min, sec.

For more details on formatting see here.

db> 
SELECT to_local_datetime('2018-05-07T15:01:22.306916');
{<local_datetime>'2018-05-07T15:01:22.306916'}
db> 
... 
SELECT to_local_datetime('May 7th, 2018 15:01:22',
                         'Mon DDth, YYYY HH24:MI:SS');
{<local_datetime>'2018-05-07T15:01:22'}
db> 
... 
SELECT to_local_datetime(
    2018, 5, 7, 15, 1, 22.306916);
{<local_datetime>'2018-05-07T15:01:22.306916'}

A timezone-aware datetime type can be converted to local datetime in the specified timezone:

db> 
... 
... 
SELECT to_local_datetime(
  <datetime>'December 31, 2018 10:00PM GMT+8',
  'US/Central');
{<local_datetime>'2019-01-01T00:00:00'}
function std::to_local_date(s: str, fmt: OPTIONAL str = {}) -> local_datestd::to_local_date(dt: datetime, zone: str) -> local_datestd::to_local_date(year: int64, month: int64, day: int64) -> local_date

Create a local_date value.

Similar to to_datetime(), the local_date value can be parsed from the input str s with an optional fmt argument or it can be given in terms of its component parts: year, month, day.

For more details on formatting see here.

db> 
SELECT to_local_date('2018-05-07');
{<local_date>'2018-05-07'}
db> 
SELECT to_local_date('May 7th, 2018', 'Mon DDth, YYYY');
{<local_date>'2018-05-07'}
db> 
SELECT to_local_date(2018, 5, 7);
{<local_date>'2018-05-07'}

A timezone-aware datetime type can be converted to local date in the specified timezone:

db> 
... 
... 
SELECT to_local_date(
  <datetime>'December 31, 2018 10:00PM GMT+8',
  'US/Central');
{<local_date>'2019-01-01'}
function std::to_local_time(s: str, fmt: OPTIONAL str = {}) -> local_timestd::to_local_time(dt: datetime, zone: str) -> local_timestd::to_local_time(hour: int64, min: int64, sec: float64) -> local_time

Create a local_time value.

Similar to to_datetime(), the local_time value can be parsed from the input str s with an optional fmt argument or it can be given in terms of its component parts: hour, min, sec.

For more details on formatting see here.

db> 
SELECT to_local_time('15:01:22.306916');
{<local_time>'15:01:22.306916'}
db> 
SELECT to_local_time('03:01:22pm', 'HH:MI:SSam');
{<local_time>'15:01:22'}
db> 
SELECT to_local_time(15, 1, 22.306916);
{<local_time>'15:01:22.306916'}

A timezone-aware datetime type can be converted to local date in the specified timezone:

db> 
... 
... 
SELECT to_local_time(
  <datetime>'December 31, 2018 10:00PM GMT+8',
  'US/Pacific');
{<local_date>'22:00:00'}
function std::to_duration(NAMED ONLY years: int64 = 0, NAMED ONLY months: int64 = 0, NAMED ONLY weeks: int64 = 0, NAMED ONLY days: int64 = 0, NAMED ONLY hours: int64 = 0, NAMED ONLY minutes: int64 = 0, NAMED ONLY seconds: float64 = 0) -> duration

Create a duration value.

This function uses NAMED ONLY arguments to create a duration value. The available duration fields are: years, months, weeks, days, hours, minutes, seconds.

db> 
... 
... 
SELECT to_duration(hours := 1,
                   minutes := 20,
                   seconds := 45);
{<duration>'1:20:45'}
db> 
SELECT to_duration(seconds := 4845);
{<duration>'1:20:45'}