Datetimes

SurrealDB has native support for datetimes with nanosecond precision. SurrealDB automatically parses and understands datetimes which are written as strings in the SurrealQL language. Times must also be formatted in an ISO-8601 format.

Note

As of v2.0.0, SurrealDB no longer eagerly converts a string into a datetime. An implicit d prefix or cast using <datetime> is required instead.

CREATE event SET time = d"2023-07-03T07:18:52Z";

SurrealDB handles all datetimes with nanosecond precision.

CREATE event SET time = d"2023-07-03T07:18:52.841147Z";

SurrealDB handles all timezones, and automatically converts and stores datetimes as a UTC date.

CREATE event SET time = d"2023-07-03T07:18:52.841147+02:00";

The above queries will all work even if the datetime format is incorrect, but will generate a string instead of a datetime. Casting with <datetime> will force an error if the input is incorrect.

With correct input:

CREATE event SET time = <datetime>"2023-07-03T07:18:52.841147Z";

Response
[[{ id: event:jwm8ncmfi30nrxdf24ws, time: d'2023-07-03T07:18:52.841147Z' }]]

With incorrect input (missing final Z):

CREATE event SET time = <datetime>"2023-07-03T07:18:52.841147";

Response
"Expected a datetime but cannot convert '2023-07-03T07:18:52.841147' into a datetime"

Note

As a convenience, a date without time will always parse correctly as a datetime.

CREATE event SET time = <datetime>"2024-04-03";

Response
"2024-03-04T00:00:00Z"

Datatime types in schemafull tables

Defining a schemafull table will also ensure that datetimes are properly formatted and not passed on as simple strings.

DEFINE TABLE event SCHEMAFULL;
DEFINE FIELD time ON event TYPE datetime;
CREATE event SET time = "2023-07-03T07:18:52.841147";

Response
"Found '2023-07-03T07:18:52.841147' for field `time`, with record `event:l4q1s7hermg9yoemkqrp`, but expected a datetime"

The above query will fail because the datetime is not cast as a datetime type. The correct query is:

DEFINE TABLE event SCHEMAFULL;
DEFINE FIELD time ON event TYPE datetime;
CREATE event SET time = d"2023-07-03T07:18:52.84114Z";

Response
[NONE, NONE, [{ id: event:a3g30bdxypvt21tf3jo0, time: d'2023-07-03T07:18:52.841140Z' }]]

Datetime comparison

A datetime can be compared with another using the advanced SurrealDB operators.

SELECT * FROM d"2023-07-03T07:18:52Z" < d"2023-07-03T07:18:52.84114Z";

Response
[[true]]

Durations and datetimes

Durations can be used to modify and alter datetimes.

CREATE event SET time = d"2023-07-03T07:18:52Z" + 2w;

Response
[[{ id: event:⟨9ey7v8r0fd46xblf9dsf⟩, time: d'2023-07-17T07:18:52Z' }]]

Multi-part durations can also be used to modify datetimes.

CREATE event SET time = d"2023-07-03T07:18:52.841147Z" + 1h30m20s1350ms;

Response
[[{ id: event:⟨9ey7v8r0fd46xblf9dsf⟩, time: d'2023-07-03T08:49:14.191147Z' }]]

Duration units

Durations can be specified in any of the following units:

Next steps

You’ve now seen how to store, modify, and handle dates and times in SurrealDB. For more advanced functionality, take a look at the time functions, which enable extracting, altering, rounding, and grouping datetimes into specific time intervals.