> ## 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
{children}
;
};
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.
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.
A time zone offset of `+00:00` is displayed for all `TIME` and `TIMESTAMP` values, but is not stored in the database.
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.
Explore the differences of `TIMESTAMP` and `TIMESTAMPTZ` in the following video:
CREATE TABLE timestamps (a INT PRIMARY KEY, b TIMESTAMPTZ);
````
``` sql
> 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 `ALTER COLUMN ... SET DATA TYPE` 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)
```
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`:
```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
* Data Types
* `DECIMAL`
* `FLOAT`
* `TIME`
* `INT`
* `DATE`
* `STRING`