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.
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
> CREATE TABLE dates (a DATE PRIMARY KEY, b INT);
> 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:
> INSERT INTO dates VALUES (DATE '2016-03-26', 12345);
String literal implicitly typed as DATE:
> INSERT INTO dates VALUES ('2016-03-27', 12345);
> 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). |
FLOAT |
Converts to number of days since the Unix epoch (Jan. 1, 1970). |
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). |
STRING |
–– |
