Types, Functions, and Operators for Dates and Times

EdgeDB has two classes of date/time types:

• a timezone-aware std::datetime type;

• a set of “local” date/time objects, not attached to any particular timezone: cal::local_datetime, cal::local_date, and cal::local_time.

There are also two different ways of measuring duration:

• duration using absolute and unambiguous units;

• cal::relative_duration using fuzzy units like years, months and days in addition to the absolute units.

All date/time operators and functions and type casts are designed to maintain a strict separation between timezone-aware and “local” date/time values.

EdgeDB stores and outputs timezone-aware values in UTC.

All the date/time types are restricted to years between 1 and 9999, including the end points.

Although many systems support ISO 8601 date formatting in theory, in practice the formatting before year 1 and after 9999 tends to be inconsistent. As such dates outside that range are not reliably portable.

type

datetime

datetime

A timezone-aware type representing a moment in time.

All dates must correspond to dates that exist in the proleptic Gregorian calendar.

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.

All datetime values are restricted to the range from year 1 to 9999.

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

type

cal::local_datetime

local_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.

All datetime values are restricted to the range from year 1 to 9999.

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

type

cal::local_date

local_date

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.

type

cal::local_time

local_time

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.

type

duration

duration

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.

type

cal::relative_duration

relative_duration

A type representing a span of time.

Unlike std::duration a relative_duration is not a precise measurement 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 2020 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', 'millennia'.

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 millennia';

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'}

If an arithmetic operation results in a day that doesn’t exist in the given month, the last day of the month is used instead.

db> 
...

select <cal::local_datetime>"2021-01-31T15:00:00" +
       <cal::relative_duration>"1 month";

{<cal::local_datetime>'2021-02-28T15:00:00'}

During arithmetic operations involving a relative_duration consisting of multiple components (units), higher-order components are applied first, followed by lower-order elements.

db> 
...

select <cal::local_datetime>"2021-04-30T15:00:00" +
       <cal::relative_duration>"1 month 1 day";

{<cal::local_datetime>'2021-05-31T15:00:00'}

Compare this to adding up the same duration components separately with higher-order units first and then lower-order, which produces the same result as above:

db> 
... 
...

select <cal::local_datetime>"2021-04-30T15:00:00" +
       <cal::relative_duration>"1 month" +
       <cal::relative_duration>"1 day";

{<cal::local_datetime>'2021-05-31T15:00:00'}

When the order is reversed the result may actually be different for some corner cases:

db> 
... 
...

select <cal::local_datetime>"2021-04-30T15:00:00" +
       <cal::relative_duration>"1 day" +
       <cal::relative_duration>"1 month";

{<cal::local_datetime>'2021-06-01T15:00:00'}

Gotchas

Due to the implementation of relative_duration logic, arithmetic operations may behave counterintuitively.

Non-associative

db> 
... 
...

select <cal::local_datetime>'2021-01-31T00:00:00' +
       <cal::relative_duration>'1 month' +
       <cal::relative_duration>'1 month';

{<cal::local_datetime>'2021-03-28T00:00:00'}

db> 
... 
...

select <cal::local_datetime>'2021-01-31T00:00:00' +
      (<cal::relative_duration>'1 month' +
       <cal::relative_duration>'1 month');

{<cal::local_datetime>'2021-03-31T00:00:00'}

Lossy

db> 
... 
... 
...

with m := <cal::relative_duration>'1 month'
select <cal::local_date>'2021-01-31' + m
       =
       <cal::local_date>'2021-01-30' + m;

{true}

Asymmetric

db> 
...

with m := <cal::relative_duration>'1 month'
select <cal::local_date>'2021-01-31' + m - m;

{<cal::local_date>'2021-01-28'}

Non-monotonic

db> 
... 
... 
...

with m := <cal::relative_duration>'1 month'
select <cal::local_datetime>'2021-01-31T01:00:00' + m
       <
       <cal::local_datetime>'2021-01-30T23:00:00' + m;

{true}

db> 
... 
... 
...

with m := <cal::relative_duration>'2 month'
select <cal::local_datetime>'2021-01-31T01:00:00' + m
       <
       <cal::local_datetime>'2021-01-30T23:00:00' + m;

{false}

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

type

cal::date_duration

date_duration

A type representing a span of time in days.

This type is only available in EdgeDB 2.0 or later.

The date_duration type is similar to relative_duration, but it only uses 2 different units under the hood: months and days. It is the result of subtracting one cal::local_date from another. The purpose of this type is to allow performing + and - operations on cal::local_date and produce cal::local_date as the result.

db> 
...

select <cal::local_date>'2022-06-30' -
  <cal::local_date>'2022-06-25';

{<cal::date_duration>'P5D'}

db> 
...

select <cal::local_date>'2022-06-25' +
  <cal::date_duration>'5 days';

{<cal::local_date>'2022-06-30'}

db> 
...

select <cal::local_date>'2022-06-25' -
  <cal::date_duration>'5 days';

{<cal::local_date>'2022-06-20'}

Valid units when converting from a string (and combinations of them): 'days', 'weeks', 'months', 'years', 'decades', 'centuries', 'millennia'.

select <cal::date_duration>'45 days';
select <cal::date_duration>'3 weeks 5 days';
select <cal::date_duration>'-7 millennia';

Generally date_duration is fully compatible with cal::relative_duration and has the same general behaviour and caveats. It will even to implicitly cast to cal::relative_duration in any situation where only cal::relative_duration is expected.

See function cal::to_date_duration() and date/time operators for more ways of working with cal::date_duration.

operator

dt + dt

datetime + duration -> datetimedatetime + cal::relative_duration -> cal::relative_durationduration + duration -> durationduration + cal::relative_duration -> cal::relative_durationcal::relative_duration + cal::relative_duration -> cal::relative_durationcal::local_datetime + cal::relative_duration -> cal::relative_durationcal::local_datetime + duration -> cal::local_datetimecal::local_time + cal::relative_duration -> cal::relative_durationcal::local_time + duration -> cal::local_timecal::local_date + cal::date_duration -> cal::local_datecal::date_duration + cal::date_duration -> cal::date_durationcal::local_date + cal::relative_duration -> cal::local_datetimecal::local_date + duration -> cal::local_datetime

Time interval addition.

This operator is commutative.

db>

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

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

db>

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

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

db>

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

{10800s}

operator

dt - dt

duration - duration -> durationdatetime - datetime -> durationdatetime - duration -> datetimedatetime - cal::relative_duration -> datetimecal::relative_duration - cal::relative_duration -> cal::relative_durationcal::local_datetime - cal::local_datetime -> cal::relative_durationcal::local_datetime - cal::relative_duration -> cal::local_datetimecal::local_datetime - duration -> cal::local_datetimecal::local_time - cal::local_time -> cal::relative_durationcal::local_time - cal::relative_duration -> cal::local_timecal::local_time - duration -> cal::local_timecal::date_duration - cal::date_duration -> cal::date_durationcal::local_date - cal::local_date -> cal::date_durationcal::local_date - cal::date_duration -> cal::local_datecal::local_date - cal::relative_duration -> cal::local_datetimecal::local_date - duration -> cal::local_datetimeduration - cal::relative_duration -> cal::relative_durationcal::relative_duration - duration -> cal::relative_duration

Time interval and date/time subtraction.

db> 
...

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

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

db> 
...

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

{-2678400s}

db> 
...

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

{-3600s}

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

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:

db> 
...

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

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

When subtracting one cal::local_date from another the result is given in whole number of days using cal::date_duration:

db> 
...

select <cal::local_date>'2022-06-25' -
  <cal::local_date>'2019-02-01';

{<cal::date_duration>'P1240D'}

function

datetime_current()

std::datetime_current() -> datetime

Return the current server date and time.

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

db> 
... 
...

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

{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

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

db> 
...

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

{1}

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

db> 
...

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

{21}

db> 
...

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

{2018}

db> 
...

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

{5}

db> 
...

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

{127}

function

duration_get()

std::duration_get(dt: duration, el: str) -> float64std::duration_get(dt: cal::relative_duration, el: str) -> float64std::duration_get(dt: cal::date_duration, el: str) -> float64

Extract a specific element of input duration by name.

Only available in EdgeDB 2.0 or later.

There units avaialble for extraction are grouped into 3 categories. The largest units category of months or greater:

• 'millennium' - number of 1000-year chunks rounded down

• 'century' - the number of centiries rounded down

• 'decade' - the number of decades rounded down

• 'year' - the numbder of years rounded down

• 'quarter'- the quarter left over after whole years are accounted for

• 'month' - the number of months left over after whole years are accounted for

The middle size units of days:

• 'day' - specifically the number of days recorded in the duration

The smallest unit category of hours, minutes, seconds, etc.:

• 'hour' - duration hours

• 'minutes' - duration minutes (0-59)

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

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

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

Additionally, it’s possible to convert a given duration into seconds:

• 'totalseconds' - the number of seconds represented by the duration. It will be approximate for cal::relative_duration and cal::date_duration with usints larger than a day, because a month is assumed to be 30 days exactly.

The duration scalar only has the smallest units avaialbe for extraction.

The cal::relative_duration scalar has all of the units avaialbe for extraction.

The cal::date_duration scalar only has the largest and middle size units avaialbe for extraction.

db> 
...

select duration_get(
  <cal::relative_duration>'400 months', 'year');

{33}

db> 
...

select duration_get(
  <cal::date_duration>'400 months', 'month');

{4}

db> 
... 
...

select duration_get(
  <cal::relative_duration>'1 month 20 days 30 hours',
  'day');

{20}

db> 
...

select duration_get(
  <cal::relative_duration>'30 hours', 'hour');

{30}

db> 
... 
...

select duration_get(
  <cal::relative_duration>'1 month 20 days 30 hours',
  'hour');

{30}

db>

select duration_get(<duration>'30 hours', 'hour');

{30}

db> 
... 
...

select duration_get(
  <cal::relative_duration>'1 month 20 days 30 hours',
  'totalseconds');

{4428000}

db> 
...

select duration_get(
  <duration>'30 hours', 'totalseconds');

{108000}

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'.

db> 
...

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

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

db> 
...

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

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

db> 
...

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

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

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) -> durationstd::duration_truncate(dt: cal::relative_duration, unit: str) -> cal::relative_duration

Truncate the input duration to a particular precision.

The valid unit values for duration are: 'microseconds', 'milliseconds', 'seconds', 'minutes', 'hours'.

In addition to the above the following are also valid for cal::relative_duration: 'days', 'weeks', 'months', 'years', 'decades', 'centuries'.

db> 
...

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

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

db> 
...

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

{<duration>'15:01:00'}

db> 
...

select duration_truncate(
  <cal::relative_duration>'400 months', 'years');

{<cal::relative_duration>'P33Y'}

db> 
...

select duration_truncate(
  <cal::relative_duration>'400 months', 'decades');

{<cal::relative_duration>'P30Y'}

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.

db>

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

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

db>

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

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

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:

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

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

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.

db>

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

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

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'}

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:

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={}) -> cal::local_datecal::to_local_date(dt: datetime, zone: str) -> cal::local_datecal::to_local_date(year: int64, month: int64, day: int64) -> cal::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.

db>

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

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

db>

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

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

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:

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.

db>

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

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

db>

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

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

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:

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.

db> 
... 
...

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

{4845s}

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.

db>

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

{3600.000000n}

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.

db>

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

{<cal::relative_duration>'P5YT1S'}

db>

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

{<cal::relative_duration>'P3M27D'}

function

cal::to_date_duration()

cal::to_date_duration( named only years: int64=0, named only months: int64=0, named only days: int64=0 ) -> cal::date_duration

Create a cal::date_duration value.

This function uses named only arguments to create a cal::date_duration value. The available duration fields are: years, months, days.

db>

select cal::to_date_duration(years := 1, days := 3);

{<cal::date_duration>'P1Y3D'}

db>

select cal::to_date_duration(days := 12);

{<cal::date_duration>'P12D'}

function

cal::duration_normalize_hours()

cal::duration_normalize_hours( dur: cal::relative_duration ) -> cal::relative_duration

Convert 24-hour chunks into days.

Only available in EdgeDB 2.0 or later.

This function converts all 24-hour chunks into day units. The resulting cal::relative_duration is guaranteed to have less than 24 hours in total in the units smaler than days.

db> 
...

select cal::duration_normalize_hours(
  <cal::relative_duration>'1312 hours');

{<cal::relative_duration>'P54DT16H'}

This is a lossless operation because 24 hours are always equal to 1 day in cal::relative_duration units.

This is sometimes used together with cal::duration_normalize_days().

function

cal::duration_normalize_days()

cal::duration_normalize_days( dur: cal::relative_duration ) -> cal::relative_durationcal::duration_normalize_days( dur: cal::date_duration ) -> cal::date_duration

Convert 30-day chunks into months.

Only available in EdgeDB 2.0 or later.

This function converts all 30-day chunks into month units. The resulting cal::relative_duration or cal::date_duration is guaranteed to have less than 30 day units.

db> 
...

select cal::duration_normalize_days(
  <cal::relative_duration>'1312 days');

{<cal::relative_duration>'P3Y7M22D'}

db> 
...

select cal::duration_normalize_days(
  <cal::date_duration>'1312 days');

{<cal::date_duration>'P3Y7M22D'}

This function is a form of approximation and does not preserve the exact duration.

This is often used together with cal::duration_normalize_hours().