Welcome to rosterme.com.au/Internals#

For full documentation on using this markup tool - visit mkdocs.org.

Commands#

  • mkdocs new [dir-name] - Create a new project.
  • mkdocs serve - Start the live-reloading docs server.
  • mkdocs build - Build the documentation site.
  • mkdocs help - Print this help message.

Project layout#

mkdocs.yml    # The configuration file.
docs/
    index.md  # The documentation homepage.
    ...       # Other markdown pages, images and other files.

#


title: TIMESTAMP summary: 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. toc: false


The TIMESTAMP data type stores a date and time pair in UTC.

Time Zone Details#

TIMESTAMP has two variants:

  • TIMESTAMP WITH TIME ZONE 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 TIMESTAMP WITH TIME ZONE does not store any time zone data.

{{site.data.alerts.callout_info}}The default session time zone is UTC, which means that by default TIMESTAMP WITH TIME ZONE values display in UTC.{{site.data.alerts.end}}

  • TIMESTAMP WITHOUT TIME ZONE presents all TIMESTAMP values in UTC.

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

Best Practices#

We recommend always using the ...WITH TIME ZONE variant because the ...WITHOUT TIME ZONE 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 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)
TIME New in v2.0: Converts to the time portion (HH:MM:SS) of the timestamp
DATE ––
STRING ––

{{site.data.alerts.callout_info}}Because the SERIAL data type represents values automatically generated by CockroachDB to uniquely identify rows, you cannot meaningfully cast other data types as SERIAL values.{{site.data.alerts.end}}

See Also#

Data Types