DATE

The DATE stores a year, month, and day.

Syntax

You can express a constant value of type DATE using an , or a string literal type DATE or type DATE.

CockroachDB also supports using uninterpreted in contexts where a DATE value is otherwise expected. By default, CockroachDB parses the following string formats for dates:

• YYYY-MM-DD
• MM-DD-YYYY
• MM-DD-YY (default)/YY-MM-DD/DD-MM-YY

To change the input format of truncated dates (e.g., 12-16-06) from MM-DD-YY to YY-MM-DD or DD-MM-YY, set the datestyle or the sql.defaults.datestyle .

Use instead of the sql.defaults.* . This allows you to set a default value for all users for any that applies during login, making the sql.defaults.* cluster settings redundant.

PostgreSQL compatibility

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  | is_hidden
--------------+-----------+-------------+----------------+-----------------------+-----------+------------
  a           | DATE      |    false    | NULL           |                       | {primary} |   false
  b           | INT8      |    true     | NULL           |                       | {primary} |   false
(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 ('03-27-16', 12345);

> SELECT * FROM dates;

      a      |   b
-------------+--------
  2016-03-26 | 12345
  2016-03-27 | 12345
(2 rows)

Supported casting and conversion

DATE values can be 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