> Full SurrealDB documentation index: https://surrealdb.com/docs/llms.txt # Dates, times and intervals The date, time, timestamp and interval types in SurrealQL: literals, range and precision, coercion into typed fields, casts, arithmetic, comparison, and how they differ from datetime and duration. _(since v4.0.0)_ SurrealQL has four types for calendar and clock values that are not a single instant in UTC: `date` for a calendar day, `time` for a time of day, `timestamp` for a date and a time of day with no time zone, and `interval` for a calendar period such as one month and two days. This page describes each type, how it converts to and from the other types, and how it behaves in arithmetic and comparison. For an instant and for a fixed length of time, use [`datetime`](/docs/reference/query-language/language-primitives/data-types/datetimes.md) and [`duration`](/docs/reference/query-language/language-primitives/data-types/durations.md). ```surql [date'2026-10-27', time'12:30:00', timestamp'2026-10-27T12:30:00', interval'1 year 2 days']; ``` ```surql title="Output" [date'2026-10-27', time'12:30:00', timestamp'2026-10-27T12:30:00', interval'P1Y2D'] ``` ## The four types | Type | Holds | Use it for | Literal | Range | Precision | | ----------- | ------------------------------------------- | ---------------------------------------------------------------- | -------------------------------- | ---------------------------------- | ------------ | | `date` | A calendar day | a birthday, a due date or a public holiday | `date'2026-10-27'` | `-262143-01-02` to `+262142-12-30` | one day | | `time` | A time of day | opening hours or a daily schedule | `time'12:30:00.123456789'` | `00:00:00` to `24:00:00` inclusive | nanoseconds | | `timestamp` | A date and a time of day, with no time zone | an appointment at a local clock time | `timestamp'2026-10-27T12:30:00'` | the same days as `date` | nanoseconds | | `interval` | A signed number of months, days and time | "one month later", a monthly renewal or a person's age | `interval'1 year 2 mons 3 days'` | each part has its own limit | microseconds | `date` and `timestamp` use the proleptic Gregorian calendar, which extends the Gregorian calendar to dates before it was introduced. Neither holds a time zone: `timestamp'2026-10-27T12:30:00'` is 12:30 on the clock on that day, in whatever place the value describes. A year with more than four digits takes a sign, as in `date'+262142-12-30'`. An `interval` keeps three separate parts: whole months, whole days, and a time part in microseconds. Each part can be negative. The months and the days each hold a 32-bit signed integer, and the time part holds a 64-bit signed count of microseconds. The parts are kept apart because months and days do not have a fixed length: one month after 31 January is 28 February, and one month after 1 March is 1 April. ## Which type to use - **To record when something happened**, such as the time a record was created or a message was sent, use a `datetime`. It is one instant in UTC, so it is the same moment for every reader. `time::now()` returns a `datetime`. - **To store a day on the calendar**, such as a birthday or a due date, use a `date`. It has no time zone, so it is the same day everywhere. A birthday stored as a `datetime` at midnight UTC is the previous day in any time zone west of UTC. - **To store a time on the clock with no date**, such as opening hours or a daily schedule, use a `time`. Adding it to a `date` gives a `timestamp` for that day. - **To store a local date and time**, such as an appointment at 09:30 in the city where it takes place, or a value from a source that records local time, use a `timestamp`. It keeps the clock time as it was written, while a `datetime` needs the UTC offset of that place before the value can be stored. - **To measure a fixed length of time**, such as a timeout or the time a request took, use a `duration`. Each unit has one length: `1d` is always 24 hours and `1y` is always 365 days. - **To move by calendar units**, such as "one month later", a monthly subscription renewal or an age in years, months and days, use an `interval`. It keeps months and days apart from the time part, so a month is as long as the month the value lands in, and a year after 1 January 2024 includes 29 February. A `duration` and an `interval` give different results in a leap year. `1y` adds 365 days, which ends one day early in 2024, while `interval'1 year'` gives the same date in the next year: ```surql d'2024-01-01T09:00:00Z' + 1y; //- d'2024-12-31T09:00:00Z' d'2024-01-01T09:00:00Z' + interval'1 year'; //- d'2025-01-01T09:00:00Z' ``` ## Literals A literal is the type name followed directly by a quoted string. Single and double quotes both work, and the value is always displayed with single quotes. ```surql date"2026-10-27"; //- date'2026-10-27' time'12:30:00.123456789'; //- time'12:30:00.123456789' time'24:00:00'; //- time'24:00:00' timestamp'2026-10-27 12:30:00.5'; //- timestamp'2026-10-27T12:30:00.500' ``` A `timestamp` takes a space or a `T` between the date and the time. It has no time zone designator, so a string ending in `Z` or `+02:00` is written as a `datetime` instead. An `interval` literal takes two forms. The first is a list of numbers and units, as in PostgreSQL, with an optional time of day at the end. The second is ISO 8601. Both forms are displayed in ISO 8601, where each part carries its own sign: ```surql interval'1 year 2 mons 3 days 04:05:06.5'; //- interval'P1Y2M3DT4H5M6.5S' interval'P1Y2M3DT4H5M6.5S'; //- interval'P1Y2M3DT4H5M6.5S' interval'2 weeks 3 hours 15 minutes'; //- interval'P14DT3H15M' interval'-1 day'; //- interval'P-1D' interval'1 day -02:00:00'; //- interval'P1DT-2H' ``` The units are `millennium`, `century`, `decade`, `year`, `month` (`mon`), `week`, `day`, `hour`, `minute` (`min`), `second` (`sec`), `millisecond` (`ms`) and `microsecond` (`us`), each in the singular or the plural. A year is 12 months and a week is 7 days. A fraction of a second smaller than one microsecond is rounded to the nearest microsecond. An unknown unit, a time later than `24:00:00` and a date that is not on the calendar are parse errors: ```surql date'2026-13-45'; //- Invalid date "2026-13-45": input is out of range time'25:00:00'; //- Invalid time: "25:00:00" interval'1 fortnight'; //- Invalid interval: "1 fortnight" ``` The `d` prefix keeps its meaning. `d'2026-10-27'` is a `datetime` at midnight UTC, and it is a different value from `date'2026-10-27'`. ```surql type::of(d'2026-10-27'); //- 'datetime' type::of(date'2026-10-27'); //- 'date' ``` ### Infinity A `date` and a `timestamp` can also be `infinity` or `-infinity`, as in PostgreSQL. `infinity` is later than every other value of its type, and `-infinity` is earlier than every other value. These two values use the first and last days of the range of the type, which is why the latest date that can be written as a calendar date is `+262142-12-30`. Arithmetic on an infinity gives the same infinity. ```surql date'infinity' > date'+262142-12-30'; //- true date'infinity' + 1; //- date'infinity' ``` A `time` and an `interval` have no infinity. ## How they differ from datetime and duration | Type | Represents | Time zone | | ----------- | ----------------------------------------------------- | -------------- | | `datetime` | An instant | UTC | | `timestamp` | A date and a time on the clock | none | | `date` | A calendar day | none | | `time` | A time of day | none | | `duration` | A fixed length of time in nanoseconds, never negative | not applicable | | `interval` | A signed number of months, days and microseconds | not applicable | A `duration` has a fixed length, so `30d` is always 30 days. An `interval` keeps months and days separately, so `interval'1 month'` moves a value to the same day of the next month, however many days that month has: ```surql d'2026-01-31T10:00:00Z' + 30d; //- d'2026-03-02T10:00:00Z' d'2026-01-31T10:00:00Z' + interval'1 month'; //- d'2026-02-28T10:00:00Z' ``` ## Typed fields and coercion A field, a function parameter or a return type declared with one of these types accepts a value of that type. It also accepts a value of another temporal type when the conversion loses nothing: - A `date` widens into a `timestamp` or a `datetime`, at midnight. - A `timestamp` widens into a `datetime`, read as UTC. - A `datetime` narrows into a `timestamp`, as its UTC date and time, and into a `date` only when it is exactly midnight UTC. Every other conversion needs a [cast](#casts). A string never coerces into any of these types, in the same way that a string does not coerce into a `datetime`. The schema below stores bookings with a day, a start time and a length. A `datetime` at exactly midnight UTC is accepted for the `date` field: ```surql DEFINE TABLE booking SCHEMAFULL; DEFINE FIELD day ON booking TYPE date; DEFINE FIELD starts ON booking TYPE time; DEFINE FIELD length ON booking TYPE interval; CREATE booking:1 CONTENT { day: date'2026-10-27', starts: time'09:30:00', length: interval'1 hour 30 minutes' }; CREATE booking:2 CONTENT { day: d'2026-10-28T00:00:00Z', starts: time'14:00:00', length: interval'2 hours' }; ``` ```surql title="Output" [{ day: date'2026-10-28', id: booking:2, length: interval'PT2H', starts: time'14:00:00' }] ``` A `datetime` that is not at midnight, a string and a `duration` are refused. The error for the `datetime` names the cast to use: ```surql CREATE booking:3 CONTENT { day: d'2026-10-28T09:00:00Z', starts: time'14:00:00', length: interval'2 hours' }; //- "Couldn't coerce value for field `day` of `booking:3`: Expected `date (a datetime that is not midnight UTC needs the cast ``)` but found `d'2026-10-28T09:00:00Z'`" CREATE booking:4 CONTENT { day: '2026-10-28', starts: time'14:00:00', length: interval'2 hours' }; //- "Couldn't coerce value for field `day` of `booking:4`: Expected `date` but found `'2026-10-28'`" CREATE booking:5 CONTENT { day: date'2026-10-28', starts: time'14:00:00', length: 2h }; //- "Couldn't coerce value for field `length` of `booking:5`: Expected `interval` but found `2h`" ``` With the two bookings stored, adding the start time to the day gives a `timestamp`, and adding the length to that gives the end: ```surql SELECT id, day + starts AS begins, day + starts + length AS ends FROM booking; ``` ```surql title="Output" [ { begins: timestamp'2026-10-27T09:30:00', ends: timestamp'2026-10-27T11:00:00', id: booking:1 }, { begins: timestamp'2026-10-28T14:00:00', ends: timestamp'2026-10-28T16:00:00', id: booking:2 } ] ``` The types also work inside `option<>`, `array<>`, `set<>` and unions such as `date | interval`, and in a `DEFAULT` clause, which is stored and displayed as the same literal. A field of any of these types can be indexed, including with a `UNIQUE` index. ## Casts The casts ``, `