TIMESTAMP / TIMESTAMPTZ

The TIMESTAMP and TIMESTAMPTZ 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 , 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 and TIMESTAMP values, but is not stored in the database.

You can use the and 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)

> 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           |                       | {primary} |   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 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           |                       | {primary} |   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           |                       | {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:

> 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

SELECT now()::int;

Convert a TIMESTAMP to milliseconds since epoch

SELECT (now()::float*1000)::int;

Convert a TIMESTAMP to microseconds since epoch

SELECT (now()::float*1000000)::int;

Convert an INT (seconds since epoch) to timestamp

SELECT 1597868006::timestamp;

Convert a STRING (seconds since epoch) to timestamp

SELECT '1597868006'::int::timestamp;

Convert an INT (milliseconds since epoch) to timestamp

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

SELECT TIMESTAMP 'epoch' + (1597868048960::float/1000)::interval;

Convert a STRING (milliseconds since epoch) to timestamp

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

SELECT TIMESTAMP 'epoch' + (1597868402212060::float/1000000)::interval;

Convert a STRING (microseconds since epoch) to timestamp

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