DATE

On this page Carat arrow pointing down
Warning:
CockroachDB v20.1 is no longer supported. For more details, see the Release Support Policy.

The DATE data type stores a year, month, and day.

Syntax

A constant value of type DATE can be expressed using an interpreted literal, or a string literal annotated with type DATE or coerced to type DATE.

The string format for dates is YYYY-MM-DD. For example: DATE '2016-12-23'.

CockroachDB also supports using uninterpreted string literals in contexts where a DATE value is otherwise expected.

Note:

DATE values in CockroachDB are fully PostgreSQL-compatible, including support for special values (e.g., +/- infinity). Existing dates outside of the PostgreSQL date range (4714-11-24 BC to 5874897-12-31) are converted to +/- infinity dates.

Size

A DATE column supports values up to 16 bytes in width, but the total storage size is likely to be larger due to CockroachDB metadata.

Examples

icon/buttons/copy
> CREATE TABLE dates (a DATE PRIMARY KEY, b INT);
icon/buttons/copy
> SHOW COLUMNS FROM dates;
+-------------+-----------+-------------+----------------+-----------------------+-------------+
| column_name | data_type | is_nullable | column_default | generation_expression |   indices   |
+-------------+-----------+-------------+----------------+-----------------------+-------------+
| a           | DATE      |    false    | NULL           |                       | {"primary"} |
| b           | INT       |    true     | NULL           |                       | {}          |
+-------------+-----------+-------------+----------------+-----------------------+-------------+
(2 rows)

Explicitly typed DATE literal:

icon/buttons/copy

> INSERT INTO dates VALUES (DATE '2016-03-26', 12345);

String literal implicitly typed as DATE:

icon/buttons/copy

> INSERT INTO dates VALUES ('2016-03-27', 12345);
icon/buttons/copy
> SELECT * FROM dates;
+---------------------------+-------+
|             a             |   b   |
+---------------------------+-------+
| 2016-03-26 00:00:00+00:00 | 12345 |
| 2016-03-27 00:00:00+00:00 | 12345 |
+---------------------------+-------+

Supported casting and conversion

DATE values can be cast to any of the following data types:

Type Details
DECIMAL Converts to number of days since the Unix epoch (Jan. 1, 1970). This is a CockroachDB experimental feature which may be changed without notice.
FLOAT Converts to number of days since the Unix epoch (Jan. 1, 1970). This is a CockroachDB experimental feature which may be changed without notice.
TIMESTAMP Sets the time to 00:00 (midnight) in the resulting timestamp.
INT Converts to number of days since the Unix epoch (Jan. 1, 1970). This is a CockroachDB experimental feature which may be changed without notice.
STRING ––

See also

Data Types


Yes No
On this page

Yes No