INTERVAL

On this page Carat arrow pointing down

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.
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'

New in v21.2: 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).

To set the intervalstyle session variable, the intervalstyle_enabled session variable must be set to true. At the beginning of each session, the intervalstyle_enabled variable is set to the value of the sql.defaults.intervalstyle.enabled cluster setting (false, by default).

Note:

When the intervalstyle_enabled session variable is set to true, you cannot cast values from INTERVAL to STRING or from STRING to INTERVAL if the value belongs to a computed column, a partially-indexed column, or a geo-partitioned column. To work around this limitation, use the to_char_with_style(interval, style) or parse_interval(interval, intervalstyle) built-in functions.

Details on SQL Standard input

Without a precision or duration field 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 ... <unit> or INTERVAL ... <unit> TO <unit>.

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

icon/buttons/copy
> CREATE TABLE intervals (a INT PRIMARY KEY, b INTERVAL);
icon/buttons/copy
> 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)
icon/buttons/copy
> 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');
icon/buttons/copy
> 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


Yes No
On this page

Yes No