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.
cal::to_local_datetime()	Create a cal::local_datetime value.
cal::to_local_date()	Create a cal::local_date value.
cal::to_local_time()	Create a cal::local_time value.
to_duration()	Create a duration value.
cal::to_relative_duration()	Create a cal::relative_duration value.
datetime_get()	Extract a specific element of input datetime by name.
cal::time_get()	Extract a specific element of input time by name.
cal::date_get()	Extract a specific element of input date by name.
datetime_truncate()	Truncate the input datetime to a particular precision.
duration_truncate()	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

dt + dt

datetime + duration -> datetimecal::local_datetime + duration -> cal::local_datetimecal::local_date + duration -> cal::local_datecal::local_time + duration -> cal::local_timeduration + duration -> durationdatetime + cal::relative_duration -> cal::relative_durationcal::local_dateiime + cal::relative_duration -> cal::relative_durationcal::local_date + cal::relative_duration -> cal::relative_durationcal::local_time + cal::relative_duration -> cal::relative_durationduration + cal::relative_duration -> cal::relative_durationcal::relative_duration + cal::relative_duration -> cal::relative_duration

Time interval addition.

This operator is commutative.

Copy

db>

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

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

Copy

db>

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

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

Copy

db>

SELECT <duration>'1 hour' + <duration>'2 hours';

{10800s}

operator

dt - dt

duration - duration -> durationdatetime - datetime -> durationcal::local_datetime - cal::local_datetime -> durationlocal_time - local_time -> durationlocal_date - local_date -> durationdatetime - duration -> datetimecal::local_datetime - duration -> cal::local_datetimelocal_time - duration -> local_timelocal_date - duration -> local_dateduration - cal::relative_duration -> cal::relative_durationcal::relative_duration - duration -> cal::relative_durationcal::relative_duration - cal::relative_duration -> cal::relative_durationdatetime - cal::relative_duration -> datetimecal::local_datetime - cal::relative_duration -> cal::local_datetimelocal_time - cal::relative_duration -> local_timelocal_date - cal::relative_duration -> local_date

Time interval and date/time subtraction.

Copy

db> 
...

SELECT <datetime>'2019-01-01T01:02:03+00' -
  <duration>'24 hours';

{<datetime>'2018-12-31T01:02:03Z'}

Copy

db> 
...

SELECT <datetime>'2019-01-01T01:02:03+00' -
  <datetime>'2019-02-01T01:02:03+00';

{-2678400s}

Copy

db> 
...

SELECT <duration>'1 hour' -
  <duration>'2 hours';

{-3600s}

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

Copy

db> 
...

SELECT <duration>'1 day' -
  <datetime>'2019-01-01T01:02:03+00';

QueryError: operator '-' cannot be applied to operands ...

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

Copy

db> 
...

SELECT <datetime>'2019-01-01T01:02:03+00' -
  <cal::local_datetime>'2019-02-01T01:02:03';

QueryError: operator '-' cannot be applied to operands ...

function

datetime_current()

std::datetime_current() -> datetime

Return the current server date and time.

Copy

db>

SELECT datetime_current();

{<datetime>'2018-05-14T20:07:11.755827Z'}

function

datetime_of_transaction()

std::datetime_of_transaction() -> datetime

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

function

datetime_of_statement()

std::datetime_of_statement() -> datetime

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

function

datetime_get()

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

Extract a specific element of input datetime by name.

The datetime scalar has the following elements available for extraction:

'epochseconds' - the number of seconds since 1970-01-01 00:00:00 UTC (Unix epoch) for datetime or local time for cal::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
'minutes' - the minutes (0-59)
'month' - the month of the year (1-12)
'quarter' - the quarter of the year (1-4)
'seconds' - 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

Copy

db> 
... 
...

SELECT datetime_get(
    <datetime>'2018-05-07T15:01:22.306916+00',
    'epochseconds');

{1525705282.306916}

Copy

db> 
... 
...

SELECT datetime_get(
    <datetime>'2018-05-07T15:01:22.306916+00',
    'year');

{2018}

Copy

db> 
... 
...

SELECT datetime_get(
    <datetime>'2018-05-07T15:01:22.306916+00',
    'quarter');

{2}

Copy

db> 
... 
...

SELECT datetime_get(
    <datetime>'2018-05-07T15:01:22.306916+00',
    'doy');

{127}

Copy

db> 
... 
...

SELECT datetime_get(
    <datetime>'2018-05-07T15:01:22.306916+00',
    'hour');

{15}

function

cal::time_get()

cal::time_get(dt: cal::local_time, el: str) -> float64

Extract a specific element of input time by name.

The cal::local_time scalar has the following elements available for extraction:

'midnightseconds'
'hour'
'microseconds'
'milliseconds'
'minutes'
'seconds'

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

Copy

db> 
...

SELECT cal::time_get(
    <cal::local_time>'15:01:22.306916', 'minutes');

{1}

Copy

db> 
...

SELECT cal::time_get(
    <cal::local_time>'15:01:22.306916', 'milliseconds');

{22306.916}

function

cal::date_get()

cal::date_get(dt: local_date, el: str) -> float64

Extract a specific element of input date by name.

The cal::local_date scalar has the following elements available for extraction:

'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)
'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.
'millennium' - the millennium. The third millennium started on Jan 1, 2001.
'month' - the month of the year (1-12)
'quarter' - the quarter of the year (1-4) 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

Copy

db> 
...

SELECT cal::date_get(
    <cal::local_date>'2018-05-07', 'century');

{21}

Copy

db> 
...

SELECT cal::date_get(
    <cal::local_date>'2018-05-07', 'year');

{2018}

Copy

db> 
...

SELECT cal::date_get(
    <cal::local_date>'2018-05-07', 'month');

{5}

Copy

db> 
...

SELECT cal::date_get(
    <cal::local_date>'2018-05-07', 'doy');

{127}

function

datetime_truncate()

std::datetime_truncate(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'
'seconds'
'minutes'
'hours'
'days'
'weeks'
'months'
'quarters'
'years'
'decades'
'centuries'

Copy

db> 
...

SELECT datetime_truncate(
    <datetime>'2018-05-07T15:01:22.306916+00', 'years');

{<datetime>'2018-01-01T00:00:00Z'}

Copy

db> 
...

SELECT datetime_truncate(
    <datetime>'2018-05-07T15:01:22.306916+00', 'quarters');

{<datetime>'2018-04-01T00:00:00Z'}

Copy

db> 
...

SELECT datetime_truncate(
    <datetime>'2018-05-07T15:01:22.306916+00', 'days');

{<datetime>'2018-05-07T00:00:00Z'}

Copy

db> 
...

SELECT datetime_truncate(
    <datetime>'2018-05-07T15:01:22.306916+00', 'hours');

{<datetime>'2018-05-07T15:00:00Z'}

function

duration_truncate()

std::duration_truncate(dt: duration, unit: str) -> duration

Truncate the input duration to a particular precision.

The valid unit values are: - 'microseconds' - 'milliseconds' - 'seconds' - 'minutes' - 'hours'

Copy

db> 
...

SELECT duration_truncate(
    <duration>'15:01:22', 'hours');

{54000s}

Copy

db> 
...

SELECT duration_truncate(
    <duration>'15:01:22.306916', 'minutes');

{54060s}

function

to_datetime()

std::to_datetime(s: str, fmt: OPTIONAL str={}) -> datetimestd::to_datetime(local: cal::local_datetime, zone: str) -> datetimestd::to_datetime(year: int64, month: int64, day: int64, hour: int64, min: int64, sec: float64, timezone: str) -> datetimestd::to_datetime(epochseconds: decimal) -> datetimestd::to_datetime(epochseconds: float64) -> datetimestd::to_datetime(epochseconds: int64) -> 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.

Copy

db>

SELECT to_datetime('2018-05-07T15:01:22.306916+00');

{<datetime>'2018-05-07T15:01:22.306916Z'}

Copy

db>

SELECT to_datetime('2018-05-07T15:01:22+00');

{<datetime>'2018-05-07T15:01:22Z'}

Copy

db> 
...

SELECT to_datetime('May 7th, 2018 15:01:22 +00',
                   'Mon DDth, YYYY HH24:MI:SS TZH');

{<datetime>'2018-05-07T15:01:22Z'}

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

Copy

db> 
...

SELECT to_datetime(
  <cal::local_datetime>'2019-01-01T01:02:03', 'HKT');

{<datetime>'2018-12-31T17:02:03Z'}

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

Copy

db> 
...

SELECT to_datetime(
    2018, 5, 7, 15, 1, 22.306916, 'UTC');

{<datetime>'2018-05-07T15:01:22.306916000Z'}

Finally, it is also possible to convert a Unix timestamp to a datetime

Copy

db>

SELECT to_datetime(1590595184.584);

{<datetime>'2020-05-27T15:59:44.584000000Z'}

function

cal::to_local_datetime()

cal::to_local_datetime(s: str, fmt: OPTIONAL str={}) -> local_datetimecal::to_local_datetime(dt: datetime, zone: str) -> local_datetimecal::to_local_datetime(year: int64, month: int64, day: int64, hour: int64, min: int64, sec: float64) -> local_datetime

Create a cal::local_datetime value.

Similar to to_datetime(), the cal::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.

Copy

db>

SELECT cal::to_local_datetime('2018-05-07T15:01:22.306916');

{<cal::local_datetime>'2018-05-07T15:01:22.306916'}

Copy

db> 
...

SELECT cal::to_local_datetime('May 7th, 2018 15:01:22',
                         'Mon DDth, YYYY HH24:MI:SS');

{<cal::local_datetime>'2018-05-07T15:01:22'}

Copy

db> 
...

SELECT cal::to_local_datetime(
    2018, 5, 7, 15, 1, 22.306916);

{<cal::local_datetime>'2018-05-07T15:01:22.306916'}

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

Copy

db> 
... 
...

SELECT cal::to_local_datetime(
  <datetime>'2018-12-31T22:00:00+08',
  'US/Central');

{<cal::local_datetime>'2018-12-31T08:00:00'}

function

cal::to_local_date()

cal::to_local_date(s: str, fmt: OPTIONAL str={}) -> local_datecal::to_local_date(dt: datetime, zone: str) -> local_datecal::to_local_date(year: int64, month: int64, day: int64) -> local_date

Create a cal::local_date value.

Similar to to_datetime(), the cal::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.

Copy

db>

SELECT cal::to_local_date('2018-05-07');

{<cal::local_date>'2018-05-07'}

Copy

db>

SELECT cal::to_local_date('May 7th, 2018', 'Mon DDth, YYYY');

{<cal::local_date>'2018-05-07'}

Copy

db>

SELECT cal::to_local_date(2018, 5, 7);

{<cal::local_date>'2018-05-07'}

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

Copy

db> 
... 
...

SELECT cal::to_local_date(
  <datetime>'2018-12-31T22:00:00+08',
  'US/Central');

{<cal::local_date>'2019-01-01'}

function

cal::to_local_time()

cal::to_local_time(s: str, fmt: OPTIONAL str={}) -> local_timecal::to_local_time(dt: datetime, zone: str) -> local_timecal::to_local_time(hour: int64, min: int64, sec: float64) -> local_time

Create a cal::local_time value.

Similar to to_datetime(), the cal::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.

Copy

db>

SELECT cal::to_local_time('15:01:22.306916');

{<cal::local_time>'15:01:22.306916'}

Copy

db>

SELECT cal::to_local_time('03:01:22pm', 'HH:MI:SSam');

{<cal::local_time>'15:01:22'}

Copy

db>

SELECT cal::to_local_time(15, 1, 22.306916);

{<cal::local_time>'15:01:22.306916'}

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

Copy

db> 
... 
...

SELECT cal::to_local_time(
  <datetime>'2018-12-31T22:00:00+08',
  'US/Pacific');

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

function

to_duration()

std::to_duration( NAMED ONLY hours: int64=0, NAMED ONLY minutes: int64=0, NAMED ONLY seconds: float64=0, NAMED ONLY microseconds: int64=0 ) -> duration

Create a duration value.

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

Copy

db> 
... 
...

SELECT to_duration(hours := 1,
                   minutes := 20,
                   seconds := 45);

{4845s}

Copy

db>

SELECT to_duration(seconds := 4845);

{4845s}

function

std::duration_to_seconds()

std::duration_to_seconds(cur: duration) -> decimal

Return duration as total number of seconds in interval.

Copy

db>

SELECT duration_to_seconds(<duration>'1 hour');

{3600.000000n}

Copy

db>

SELECT duration_to_seconds(<duration>'10 second 123 ms');

{10.123000n}

function

cal::to_relative_duration()

cal::to_relative_duration( NAMED ONLY years: int64=0, NAMED ONLY months: int64=0, NAMED ONLY days: int64=0, NAMED ONLY hours: int64=0, NAMED ONLY minutes: int64=0, NAMED ONLY seconds: float64=0, NAMED ONLY microseconds: int64=0 ) -> cal::relative_duration

Create a cal::relative_duration value.

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

Copy

db>

SELECT cal::to_relative_duration(years := 5, minutes := 1);

{P5YT1S}

Copy

db>

SELECT cal::to_relative_duration(months := 3, days := 27);

{P3M27D}

Functions and OperatorsString

Functions and OperatorsJSON

Product

Home Install Roadmap

Resources

Documentation Tutorial Easy EdgeDB Community

Company

Careers Press Kit

Newsletter

Subscribe to our mailing list to be the first to know about new blog posts and announcements.