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 |
–– |
See also
Was this helpful?
