> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# TIMESTAMP / TIMESTAMPTZ

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

The `TIMESTAMP` and `TIMESTAMPTZ` <InternalLink path="data-types">data types</InternalLink> 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.
</Note>

The difference between these two variants is that `TIMESTAMPTZ` uses the client's session <InternalLink path="set-vars#set-time-zone">time zone</InternalLink>, while the other simply does not. This behavior extends to functions like `now()` and `extract()` on `TIMESTAMPTZ` values.

<Note>
  A time zone offset of `+00:00` is displayed for all <InternalLink path="time">`TIME`</InternalLink> and `TIMESTAMP` values, but is not stored in the database.
</Note>

You can use the <InternalLink path="functions-and-operators">`timezone()`</InternalLink> and <InternalLink path="functions-and-operators#special-syntax-forms">`AT TIME ZONE`</InternalLink> 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 <InternalLink path="set-vars#set-time-zone">setting a session time zone offset</InternalLink> for your database.

## Aliases

In CockroachDB, the following are aliases:

* `TIMESTAMP`, `TIMESTAMP WITHOUT TIME ZONE`
* `TIMESTAMPTZ`, `TIMESTAMP WITH TIME ZONE`

## Syntax

You can express a constant value of type `TIMESTAMP`/`TIMESTAMPTZ` using an <InternalLink path="sql-constants#interpreted-literals">interpreted literal</InternalLink>, or a string literal <InternalLink path="scalar-expressions#explicitly-typed-expressions">annotated with</InternalLink> type `TIMESTAMP`/`TIMESTAMPTZ` or <InternalLink path="scalar-expressions#explicit-type-coercions">coerced to</InternalLink> type `TIMESTAMP`/`TIMESTAMPTZ`. When it is unambiguous, a simple unannotated <InternalLink path="sql-constants#string-literals">string literal</InternalLink> is automatically interpreted as type `TIMESTAMP` or `TIMESTAMPTZ`.

You can express `TIMESTAMP` constants 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'`. The fractional portion is optional and is rounded to microseconds (6 digits after decimal) for compatibility with the PostgreSQL wire protocol.

By default, CockroachDB interprets truncated dates (e.g., `12-16-06`) as `MM-DD-YY`. To change the input string format of truncated dates, set the `datestyle` <InternalLink path="set-vars">session variable</InternalLink> or the `sql.defaults.datestyle ` <InternalLink path="cluster-settings">cluster setting</InternalLink>.

<Note>
  Use <InternalLink path="alter-role#set-default-session-variable-values-for-all-users">`ALTER ROLE ALL SET {sessionvar} = {val}`</InternalLink> instead of the `sql.defaults.*` <InternalLink path="cluster-settings">cluster settings</InternalLink>. This allows you to set a default value for all users for any <InternalLink path="set-vars">session variable</InternalLink> that applies during login, making the `sql.defaults.*` cluster settings redundant.
</Note>

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

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 <InternalLink path="alter-table#alter-column">`ALTER COLUMN ... SET DATA TYPE`</InternalLink> 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](#create-a-table-with-a-timestamp-typed-column-with-precision).

## Supported casting and conversion

`TIMESTAMP` values can be <InternalLink path="data-types#data-type-conversions-and-casts">cast</InternalLink> to any of the following data types:

| Type      | Details                                                                                                        |
| --------- | -------------------------------------------------------------------------------------------------------------- |
| `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`    | Converts to the time portion (`HH:MM:SS`) of the timestamp.                                                    |
| `INT`     | Converts to number of seconds since the Unix epoch (Jan. 1, 1970).                                             |
| `DATE`    | Converts to the date (`YYYY-MM-DD`) of the timestamp and omits the other information.                          |
| `STRING`  | Converts to the date and time portion (`YYYY-MM-DD HH:MM:SS`) of the timestamp and omits the time zone offset. |

### Infinity `TIMESTAMP` casts

CockroachDB 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, and `-infinity::TIMESTAMP` evaluates to `-4714-11-24 00:00:00+00:00`, the minimum `TIMESTAMP` value supported.

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 `-4714-11-24 00:00:00+00:00`).

## Examples

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE timestamps (a INT PRIMARY KEY, b TIMESTAMPTZ);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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           |                       | {primary} |   false
(2 rows)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO timestamps VALUES (1, TIMESTAMPTZ '2016-03-26 10:10:10-05:00'), (2, TIMESTAMPTZ '2016-03-26');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE timestamps (a INT PRIMARY KEY, b TIMESTAMP(3));
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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           |                       | {primary} |   false
(2 rows)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO timestamps VALUES (1, TIMESTAMP '2020-03-25 12:00:00.123456'), (2, TIMESTAMP '2020-03-26 4:00:00.123456');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 <InternalLink path="alter-table#alter-column">`ALTER COLUMN ... SET DATA TYPE`</InternalLink> statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER TABLE timestamps ALTER COLUMN b SET DATA TYPE TIMESTAMP(4);
```

```
ALTER TABLE
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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           |                       | {primary} |   false
(2 rows)
```

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER TABLE timestamps ALTER COLUMN b SET DATA TYPE TIMESTAMPTZ(5);
```

```
ALTER TABLE
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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           |                       | {primary} |   false
(2 rows)
```

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

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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
```

### Convert a `TIMESTAMP` to seconds since epoch

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT now()::int;
```

### Convert a `TIMESTAMP` to milliseconds since epoch

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT (now()::float*1000)::int;
```

### Convert a `TIMESTAMP` to microseconds since epoch

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT (now()::float*1000000)::int;
```

### Convert an `INT` (seconds since epoch) to timestamp

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT 1597868006::timestamp;
```

### Convert a `STRING` (seconds since epoch) to timestamp

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT '1597868006'::int::timestamp;
```

### Convert an `INT` (milliseconds since epoch) to timestamp

Note that `TIMESTAMP epoch'` is the equivalent of `0::timestamp`.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT TIMESTAMP 'epoch' + (1597868048960::float/1000)::interval;
```

### Convert a `STRING` (milliseconds since epoch) to timestamp

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT TIMESTAMP 'epoch' + ('1597868048960'::float/1000)::interval;
SELECT TIMESTAMP 'epoch' + ('1597868048960'::INT8) * '1 ms'::interval;
SELECT TIMESTAMP 'epoch' + ('1597868048960'::INT8) * '1 millisecond'::interval;
SELECT TIMESTAMP 'epoch' + ('1597868048960' || 'ms')::interval;
SELECT TIMESTAMP 'epoch' + ('1597868048960' || 'milliseconds')::interval;
```

### Convert an `INT` (microseconds since epoch) to timestamp

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT TIMESTAMP 'epoch' + (1597868402212060::float/1000000)::interval;
```

### Convert a `STRING` (microseconds since epoch) to timestamp

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT TIMESTAMP 'epoch' + ('1597868402212060'::INT8) * '1 μs'::interval;
SELECT TIMESTAMP 'epoch' + ('1597868402212060'::INT8) * '1 microsecond'::interval;
SELECT TIMESTAMP 'epoch' + ('1597868402212060' || ' μs')::interval;
SELECT TIMESTAMP 'epoch' + ('1597868402212060' || ' microseconds')::interval;
SELECT TIMESTAMP 'epoch' + ('1597868402212060'::float/1000000)::interval;
```

## See also

* <InternalLink path="data-types">Data Types</InternalLink>
* <InternalLink path="decimal">`DECIMAL`</InternalLink>
* <InternalLink path="float">`FLOAT`</InternalLink>
* <InternalLink path="time">`TIME`</InternalLink>
* <InternalLink path="int">`INT`</InternalLink>
* <InternalLink path="date">`DATE`</InternalLink>
* <InternalLink path="string">`STRING`</InternalLink>