TIMESTAMP

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.

Variants

TIMESTAMP has two variants:

• TIMESTAMP presents all TIMESTAMP values in UTC.

• TIMESTAMPTZ converts TIMESTAMP values from UTC to the client's session time zone (unless another time zone is specified for the value). However, it is conceptually important to note that TIMESTAMPTZ does not store any time zone data.

Note:

The default session time zone is UTC, which means that by default TIMESTAMPTZ values display in UTC.

The difference between these two variants is that TIMESTAMPTZ uses the client's session time zone, while the other simply does not. This behavior extends to functions like now() and extract() on TIMESTAMPTZ values.

Best practices

We recommend always using the TIMESTAMPTZ variant because the TIMESTAMP variant can sometimes lead to unexpected behaviors when it ignores a session offset. However, we also recommend you avoid setting a session time for your database.

Aliases

In CockroachDB, the following are aliases:

• TIMESTAMP, TIMESTAMP WITHOUT TIME ZONE
• TIMESTAMPTZ, TIMESTAMP WITH TIME ZONE

Syntax

A constant value of type TIMESTAMP/TIMESTAMPTZ can be expressed using aninterpreted literal, or astring literalannotated withtype TIMESTAMP/TIMESTAMPTZ orcoerced to typeTIMESTAMP/TIMESTAMPTZ.

TIMESTAMP constants can be expressed using thefollowing string literal formats:

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

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

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

Size

A TIMESTAMP/TIMESTAMPTZ 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;

+-------------+--------------------------+-------------+----------------+-----------------------+-------------+
| column_name |        data_type         | is_nullable | column_default | generation_expression |   indices   |
+-------------+--------------------------+-------------+----------------+-----------------------+-------------+
| a           | INT                      |    false    | NULL           |                       | {"primary"} |
| 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 and conversion

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

See also

Data Types

Was this page helpful?
YesNo