The INTERVAL
data type stores a value that represents a span of time.
Syntax
A constant value of type INTERVAL
can be expressed using an
interpreted literal, or a
string literal
annotated with
type INTERVAL
or
coerced to type
INTERVAL
.
INTERVAL
constants can be expressed using the following formats:
Format | Description |
---|---|
SQL Standard | INTERVAL 'Y-M D H:M:S' Y-M D : Using a single value defines days only; using two values defines years and months. Values must be integers.H:M:S : Using a single value defines seconds only; using two values defines hours and minutes. Values can be integers or floats.Note that each side is optional. |
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' |
Golang | INTERVAL '1h2m3s4ms5us6ns' Note that ms is milliseconds, us is microseconds, and ns is nanoseconds. Also, all fields support both integers and floats. |
CockroachDB also supports using uninterpreted
string literals in contexts
where a INTERVAL
value is otherwise expected.
Intervals are stored internally as months, days, and nanoseconds.
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.
Example
> CREATE TABLE intervals (a INT PRIMARY KEY, b INTERVAL);
> SHOW COLUMNS FROM intervals;
+-------------+-----------+-------------+----------------+-----------------------+-------------+
| column_name | data_type | is_nullable | column_default | generation_expression | indices |
+-------------+-----------+-------------+----------------+-----------------------+-------------+
| a | INT | false | NULL | | {"primary"} |
| b | INTERVAL | true | NULL | | {} |
+-------------+-----------+-------------+----------------+-----------------------+-------------+
(2 rows)
> INSERT INTO intervals VALUES
(1, INTERVAL '1h2m3s4ms5us6ns'),
(2, INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds'),
(3, INTERVAL '1-2 3 4:5:6');
INSERT 3
> SELECT * FROM intervals;
+---+------------------+
| a | b |
+---+------------------+
| 1 | 1h2m3.004005006s |
| 2 | 14m3d4h5m6s |
| 3 | 14m3d4h5m6s |
+---+------------------+
(3 rows)
Supported casting and conversion
INTERVAL
values can be cast to any of the following data types:
Type | Details |
---|---|
INT |
Converts to number of seconds (second precision) |
DECIMAL |
Converts to number of seconds (nanosecond precision) |
FLOAT |
Converts to number of seconds (microsecond precision) |
STRING |
Converts to h-m-s format (nanosecond precision) |
TIME |
Converts to HH:MM:SS.SSSSSS , the time equivalent to the interval after midnight (microsecond precision) |