The TIMESTAMP data type stores a date and time pair in UTC, whereas TIMESTAMPTZ stores a date and time pair with a time zone offset from UTC.

Aliases

In CockroachDB, TIMESTAMP WITHOUT TIME ZONE is an alias for TIMESTAMP and TIMESTAMP WITH TIME ZONE is an alias for TIMESTAMPTZ.

Syntax

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

TIMESTAMP constants can be expressed using the following string literal formats:

Format Example
Date only TIMESTAMP '2016-01-25'
Date and Time TIMESTAMP '2016-01-25 10:10:10.555555'
ISO 8601 TIMESTAMP '2016-01-25T10:10:10.555555'

To express a TIMESTAMPTZ value (with time zone offset from UTC), use the following format: TIMESTAMPTZ '2016-01-25 10:10:10.555555-05:00'

When it is unambiguous, a simple unannotated string literal can also be automatically interpreted as type TIMESTAMP or TIMESTAMPTZ.

Note that the fractional portion is optional and is rounded to microseconds (6 digits after decimal) for compatibility with the PostgreSQL wire protocol.

Size

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

Examples

> CREATE TABLE timestamps (a INT PRIMARY KEY, b TIMESTAMPTZ);

> SHOW COLUMNS FROM timestamps;
+-------+--------------------------+-------+---------+
| Field |           Type           | Null  | Default |
+-------+--------------------------+-------+---------+
| a     | INT                      | false | NULL    |
| b     | TIMESTAMP WITH TIME ZONE | true  | NULL    |
+-------+--------------------------+-------+---------+
(2 rows)
> INSERT INTO timestamps VALUES (1, TIMESTAMPTZ '2016-03-26 10:10:10-05:00'), (2, TIMESTAMPTZ '2016-03-26');

> SELECT * FROM timestamps;
+---+---------------------------+
| a |             b             |
+---+---------------------------+
| 1 | 2016-03-26 15:10:10+00:00 |
| 2 | 2016-03-26 00:00:00+00:00 |
+---+---------------------------+
# Note that the first timestamp is UTC-05:00, which is the equivalent of EST.

Supported Casting & Conversion

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

Type Details
INT Converts to number of seconds since the Unix epoch (Jan. 1, 1970)
SERIAL Converts to number of seconds since the Unix epoch (Jan. 1, 1970)
DECIMAL Converts to number of seconds since the Unix epoch (Jan. 1, 1970)
FLOAT Converts to number of seconds since the Unix epoch (Jan. 1, 1970)
DATE ––
STRING ––
Because the SERIAL data type represents values automatically generated CockroachDB to uniquely identify rows, you cannot meaningfully cast other data types as SERIAL values.

See Also

Data Types



Yes No