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
.
All date/time functions and operators 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.
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
.
Scalar type SDL, DDL, introspection, and date/time functions and operators.