Timezone-aware point in time | |
Absolute time span | |
Date and time w/o timezone | |
Date type | |
Time type | |
Relative time span | |
Time interval addition. | |
Time interval and date/time subtraction. | |
Comparison operators | |
Render a date/time value to a string. | |
Create a datetime value. | |
Create a cal::local_datetime value. | |
Create a cal::local_date value. | |
Create a cal::local_time value. | |
Create a duration value. | |
Create a cal::relative_duration value. | |
Extract a specific element of input datetime by name. | |
Extract a specific element of input time by name. | |
Extract a specific element of input date by name. | |
Truncate the input datetime to a particular precision. | |
Truncate the input duration to a particular precision. | |
Return the current server date and time. | |
Return the date and time of the start of the current transaction. | |
Return the date and time of the start of the current statement. |
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.
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
.
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
.
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
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 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'
- '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'}
db> ... ...
select <cal::local_date>'2021-01-31' +
<cal::relative_duration>'12 hours' +
<cal::relative_duration>'12 hours';
{<cal::local_date>'2021-01-31'}
db> ... ...
select <cal::local_date>'2021-01-31' +
(<cal::relative_duration>'12 hours' +
<cal::relative_duration>'12 hours');
{<cal::local_date>'2021-02-01'}
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
.
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}
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 ...
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}
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}
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}
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'}
Truncate the input duration to a particular precision.
The valid unit values are:
- 'microseconds'
- 'milliseconds'
- 'seconds'
- 'minutes'
- 'hours'
db> ...
select duration_truncate(
<duration>'15:01:22', 'hours');
{54000s}
db> ...
select duration_truncate(
<duration>'15:01:22.306916', 'minutes');
{54060s}
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'}
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'}
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'}
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'}
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}
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);
{P5YT1S}
db>
select cal::to_relative_duration(months := 3, days := 27);
{P3M27D}