> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# INTERVAL
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
The `INTERVAL` data type stores a value that represents a span of time.
## Syntax
You can express a constant value of type `INTERVAL` using an interpreted literal, or a string literal annotated with type `INTERVAL` or coerced to type `INTERVAL`. CockroachDB also supports using uninterpreted string literals in contexts where an `INTERVAL` value is otherwise expected.
`INTERVAL` constants can be expressed using the following formats:
| Format | Description |
| ---------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| SQL Standard | `INTERVAL 'Y-M D H:M:S'`
You can express seconds and days as integers or floats. All other input values must be expressed as integers.
For more details, see [Details on SQL Standard input](#details-on-sql-standard-input). |
| ISO 8601 | `INTERVAL 'P1Y2M3DT4H5M6S'` |
| Traditional PostgreSQL | `INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds'` |
| Abbreviated PostgreSQL | `INTERVAL '1 yr 2 mons 3 d 4 hrs 5 mins 6 secs'` |
By default, CockroachDB displays `INTERVAL` values in the traditional PostgreSQL format (e.g., `INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds'`). To change the display format of `INTERVAL` values, set the `intervalstyle` session variable or the `sql.defaults.intervalstyle` cluster setting to a supported format (`iso_8601` for the ISO 8601 format; `sql_standard` for the SQL Standard format).
The value of `intervalstyle` affects how CockroachDB parses certain `INTERVAL` values. Specifically, when `intervalstyle = 'sql_standard'`, and when the `INTERVAL` value begins with a negative symbol, CockroachDB parses all fields as negative values (e.g., `-3 years 1 day` is parsed as `-(3 years 1 day)`, or `-3 years, -1 day`). When `intervalstyle = 'postgres'` (the default format), and when the `INTERVAL` value begins with a negative symbol, CockroachDB only applies the negative symbol to the field that it directly precedes (e.g., `-3 years 1 day` is parsed as `-3 years, +1 day`).
Use `ALTER ROLE ALL SET {sessionvar} = {val}` instead of the `sql.defaults.*` cluster settings. This allows you to set a default value for all users for any session variable that applies during login, making the `sql.defaults.*` cluster settings redundant.
### Details on SQL Standard input
Without a [precision](#precision) or [duration field](#duration-fields) specified, expect the following behavior from SQL Standard input (`Y-M D H:M:S`):
* Using a single value defines seconds only.
For example, `INTERVAL '1'` is parsed as `00:00:01`.
* Using two colon-separated integers defines hours and minutes.
For example, `INTERVAL '1:2'` is parsed as `01:02:00`.
* If the second of two colon-separated values is a float, the interval is parsed as minutes and seconds (`M:S.fff`).
For example, `INTERVAL '1:2.345'` is parsed as `00:01:02.345`.
* If the first element of the input directly preceding a colon is specified as a float, the interval is parsed as `D H:M`.
For example, `INTERVAL '1.2:03:04'` is parsed as `1 day 07:52:00`.
* If the day is omitted, no day value will be stored.
For example, `INTERVAL '1-2 3:4:5'` is parsed as `1 year 2 mons 03:04:05`, and `INTERVAL 1-2` is parsed as `1 year 2 mons`.
* If the year and month are omitted, no year or month value will be stored.
For example, `INTERVAL '1 2:3:4` is parsed as `1 day 02:03:04`.
## Size
An `INTERVAL` column supports values up to 24 bytes in width, but the total storage size is likely to be larger due to CockroachDB metadata. Intervals are stored internally as months, days, and microseconds.
## Precision
CockroachDB supports precision levels from 0 (seconds) to 6 (microseconds) for `INTERVAL` values. Precision in time values specifies the number of fractional digits retained in the seconds field. By default, `INTERVAL` values have a precision of 6 (microseconds).
For example, specifying an `INTERVAL` value as `INTERVAL(3)` truncates the time precision to milliseconds.
## Duration fields
CockroachDB supports duration fields for `INTERVAL` values. You can specify `SECOND`, `MINUTE`, `HOUR`, or `DAY` units of duration in the form `INTERVAL ... ` or `INTERVAL ... TO `.
Specifying a single duration field truncates the interval at the unit specified, defining the interval as having the duration field unit as its least-significant unit. For example, `INTERVAL '1 2:03:04' HOUR` truncates the input to an exact hour, and parses the interval as `1 day 02:00:00`.
A single duration field can also resolve ambiguity in the input. For example, `INTERVAL '1'` parses the interval as `00:00:01` (1 second). `INTERVAL '1' MINUTE` parses the interval as `00:01:00` (1 minute).
If the interval input is ambiguous, specifying two duration fields stores the interval in the units specified. For example, `INTERVAL '02:03' MINUTE TO SECOND` parses the interval as `00:02:03` (in minutes and seconds). Without `MINUTE TO SECOND`, the input would be parsed as `02:03:00` (in hours and minutes).
## Example
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE intervals (a INT PRIMARY KEY, b INTERVAL);
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW COLUMNS FROM intervals;
```
```
column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden
--------------+-----------+-------------+----------------+-----------------------+-----------+------------
a | INT8 | false | NULL | | {primary} | false
b | INTERVAL | true | NULL | | {primary} | false
(2 rows)
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO
intervals
VALUES (1, INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds'),
(2, INTERVAL '1-2 3 4:5:6'),
(3, '1-2 3 4:5:6');
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM intervals;
```
```
a | b
----+--------------------------------
1 | 1 year 2 mons 3 days 04:05:06
2 | 1 year 2 mons 3 days 04:05:06
3 | 1 year 2 mons 3 days 04:05:06
(3 rows)
```
## Supported casting and conversion
You can cast `INTERVAL` values to any of the following data types:
| Type | Details |
| --------- | --------------------------------------------------------------------------------------------------------- |
| `INT` | Converts to number of seconds (second precision) |
| `DECIMAL` | Converts to number of seconds (microsecond precision) |
| `FLOAT` | Converts to number of seconds (microsecond precision) |
| `STRING` | Converts to `h-m-s` format (microsecond precision) |
| `TIME` | Converts to `HH:MM:SS.SSSSSS`, the time equivalent to the interval after midnight (microsecond precision) |
## See also
Data Types