# TIMESTAMP / TIMESTAMPTZ

The `TIMESTAMP` and `TIMESTAMPTZ` data types store a date and time pair in 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.

You can use the `timezone()` and `AT TIME ZONE` functions to convert a `TIMESTAMPTZ` into a `TIMESTAMP` at a specified timezone, or to convert a `TIMESTAMP` into a `TIMESTAMPTZ` at a specified timezone.

## 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 zone offset 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.

Note:

A time zone offset of `+00:00` is displayed for all `TIME` and `TIMESTAMP` values, but is not stored in the database.

## 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.

## Precision

New in v20.1: CockroachDB supports precision levels from 0 (seconds) to 6 (microseconds) for `TIMESTAMP`/`TIMESTAMPTZ` values. Precision in time values specifies the number of fractional digits retained in the seconds field. For example, specifying a `TIMESTAMPTZ` value as `TIMESTAMPTZ(3)` truncates the time component to milliseconds. By default, `TIMESTAMP`/`TIMESTAMPTZ` values have a precision of 6 (microseconds).

You can use an `ALTER COLUMN ... SET DATA TYPE` statement to change the precision level of a `TIMESTAMP`/`TIMESTAMPTZ`-typed column. If there is already a non-default precision level specified for the column, the precision level can only be changed to an equal or greater precision level. For an example, see Create a table with a `TIMESTAMP`-typed column, with precision.

## Examples

### Create a table with a `TIMESTAMPTZ`-typed column

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

    > SHOW COLUMNS FROM timestamps;

      column_name |  data_type  | is_nullable | column_default | generation_expression |  indices  | is_hidden
    +-------------+-------------+-------------+----------------+-----------------------+-----------+-----------+
      a           | INT8        |    false    | NULL           |                       | {primary} |   false
      b           | TIMESTAMPTZ |    true     | NULL           |                       | {}        |   false
    (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
    (2 rows)

### Create a table with a `TIMESTAMP`-typed column, with precision

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

    > SHOW COLUMNS FROM timestamps;

      column_name |  data_type   | is_nullable | column_default | generation_expression |  indices  | is_hidden
    --------------+--------------+-------------+----------------+-----------------------+-----------+------------
      a           | INT8         |    false    | NULL           |                       | {primary} |   false
      b           | TIMESTAMP(3) |    true     | NULL           |                       | {}        |   false
    (2 rows)

    > INSERT INTO timestamps VALUES (1, TIMESTAMP '2020-03-25 12:00:00.123456'), (2, TIMESTAMP '2020-03-26 4:00:00.123456');

    > SELECT * FROM timestamps;

      a |               b
    ----+--------------------------------
      1 | 2020-03-25 12:00:00.123+00:00
      2 | 2020-03-26 04:00:00.123+00:00
    (2 rows)

To change the precision level of a column, you can use an `ALTER COLUMN ... SET DATA TYPE` statement:

    > ALTER TABLE timestamps ALTER COLUMN b SET DATA TYPE TIMESTAMP(4);

    ALTER TABLE

    > SHOW COLUMNS FROM timestamps;

      column_name |  data_type   | is_nullable | column_default | generation_expression |  indices  | is_hidden
    --------------+--------------+-------------+----------------+-----------------------+-----------+------------
      a           | INT8         |    false    | NULL           |                       | {primary} |   false
      b           | TIMESTAMP(4) |    true     | NULL           |                       | {}        |   false
    (2 rows)

When changing precision level, `TIMESTAMP` can be changed to `TIMESTAMPTZ`, and `TIMESTAMPTZ` can be changed to `TIMESTAMP`:

    > ALTER TABLE timestamps ALTER COLUMN b SET DATA TYPE TIMESTAMPTZ(5);

    ALTER TABLE

    > SHOW COLUMNS FROM timestamps;

      column_name |   data_type    | is_nullable | column_default | generation_expression |  indices  | is_hidden
    --------------+----------------+-------------+----------------+-----------------------+-----------+------------
      a           | INT8           |    false    | NULL           |                       | {primary} |   false
      b           | TIMESTAMPTZ(5) |    true     | NULL           |                       | {}        |   false
    (2 rows)

Note:

If a non-default precision level has already been specified, you cannot change the precision to a lower level.

In this case, the `b` column, which is of type `TIMESTAMPTZ(5)`, cannot be changed to a precision level below `5`:

    > ALTER TABLE timestamps ALTER COLUMN b SET DATA TYPE TIMESTAMPTZ(3);

    ERROR: unimplemented: type conversion from TIMESTAMPTZ(5) to TIMESTAMPTZ(3) requires overwriting existing values which is not yet implemented
    SQLSTATE: 0A000

## Supported casting and conversion

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

Type | Details
--- | ---
`DECIMAL` | Converts to number of seconds since the Unix epoch (Jan. 1, 1970). This is a CockroachDB experimental feature which may be changed without notice.
`FLOAT` | Converts to number of seconds since the Unix epoch (Jan. 1, 1970). This is a CockroachDB experimental feature which may be changed without notice.
`TIME` | Converts to the time portion (HH:MM:SS) of the timestamp.
`INT` | Converts to number of seconds since the Unix epoch (Jan. 1, 1970). This is a CockroachDB experimental feature which may be changed without notice.
`DATE` | --
`STRING` | --

### Infinity `TIMESTAMP` casts

CockroachDB currently does not support an `infinity`/`-infinity` representation for `TIMESTAMP` casts. Instead, `infinity::TIMESTAMP` evaluates to `294276-12-31 23:59:59.999999+00:00`, the maximum `TIMESTAMP` value supported by PostgreSQL, and `-infinity::TIMESTAMP` evaluates to `-4713-11-24 00:00:00+00:00`, the minimum `TIMESTAMP` value supported by PostgreSQL.

Note that this behavior differs from PostgreSQL, for which `infinity` is higher than any allowable `TIMESTAMP` value (including `294276-12-31 23:59:59.999999+00:00`), and `-infinity` is lower than any allowable `TIMESTAMP` value (including `-4713-11-24 00:00:00+00:00`).

For more details, see tracking issue.

## See also

Data Types