Menu

PostgreSQL TIMESTAMP Data Type

PostgreSQL TIMESTAMP stores date/time fields without zone conversion; TIMESTAMPTZ stores a UTC instant and displays it in the session time zone.

Updated on

Choose TIMESTAMP for date/time fields without zone conversion, or TIMESTAMPTZ for an instant that PostgreSQL converts to and from UTC.

PostgreSQL provides two date/time types with different time-zone semantics:

  • TIMESTAMP (or TIMESTAMP WITHOUT TIME ZONE) stores the date and clock-time fields as written; it does not convert them between time zones.
  • TIMESTAMPTZ (or TIMESTAMP WITH TIME ZONE) represents an instant. An input with an explicit offset is converted to UTC; an input without an offset is assumed to be in the session TimeZone. PostgreSQL stores the instant in UTC and displays it in the current session time zone. The original input offset and time-zone name are not retained.

Both TIMESTAMP and TIMESTAMPTZ use 8 bytes to store a value.

See PostgreSQL’s date/time type documentation for ranges, precision, and input rules.

PostgreSQL timestamp syntax

To create a column of type timestamp without a time zone, use the following syntax:

column_name TIMESTAMP column_constraint

To create a column of type timestamp with a time zone, use the following syntax:

column_name TIMESTAMPTZ column_constraint

Use an ISO date/time literal such as 2022-08-30 11:11:11. When inserting into a TIMESTAMPTZ column, include an explicit offset if the input is not in the session time zone, for example 2022-08-30 11:11:11-04.

PostgreSQL timestamp Examples

Create a new table named test_timestamp:

CREATE TABLE test_timestamp (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  timestamp_v TIMESTAMP NOT NULL DEFAULT now(),
  timestamptz_v TIMESTAMPTZ NOT NULL DEFAULT now()
);

The test_timestamp table consists of three columns:

  1. The id column is the primary key column that identifies each row, and it is an identity column.
  2. The timestamp_v column is of timestamp without a time zone type, it cannot be null, and defaults to now() the current time produced by the function.
  3. The timestamptz_v column of a timestamp with a time zone type, it cannot be null, and defaults to now() the current time produced by the function.

Insert timestamp data

To insert data into a timestamp column, you must ensure that the data is in the correct format. The following INSERT statement inserts a new row into the test_timestamp table.

INSERT INTO test_timestamp (timestamp_v, timestamptz_v)
VALUES
    ('2022-08-30 10:10:10', '2022-08-30 10:10:10'),
    ('2022-08-30', '2022-08-30')
RETURNING *;

 id |     timestamp_v     |     timestamptz_v
----+---------------------+------------------------
  1 | 2022-08-30 10:10:10 | 2022-08-30 10:10:10+08
  2 | 2022-08-30 00:00:00 | 2022-08-30 00:00:00+08
(2 rows)

The TIMESTAMPTZ input above has no explicit offset, so PostgreSQL interprets it in the session time zone (Asia/Shanghai in this example), converts it to UTC internally, and displays it back in that session zone. Changing the session TimeZone changes the displayed clock time, not the stored instant.

To see the time zone of the current session, use the SHOW TIMEZONE statement as follows:

SHOW TIMEZONE;

   TimeZone
---------------
 Asia/Shanghai
(1 row)

You cannot specify a time type value for the timestamp type, PostgreSQL will give an error as follows:

INSERT INTO test_timestamp (timestamp_v, timestamptz_v)
VALUES ('10:10:10', '10:10:10')
RETURNING *;

ERROR:  invalid input syntax for type timestamp: "10:10:10"
LINE 2: VALUES ('10:10:10', '10:10:10')

Update TIMESTAMP data

To update TIMESTAMP data, use this UPDATE statement and pass in a properly formatted value:

UPDATE
  test_timestamp
SET
  timestamp_v = '2022-08-30 11:11:11',
  timestamptz_v = '2022-08-30 11:11:11'
WHERE id = 1
RETURNING *;

 id |     timestamp_v     |     timestamptz_v
----+---------------------+------------------------
  1 | 2022-08-30 11:11:11 | 2022-08-30 11:11:11+08
(1 row)

Use TIMESTAMP data in WHERE conditions

You can use the TIMESTAMP column to filter data in the WHERE clause.

To find all rows whose timestamp_v falls on August 30, 2022, use a half-open range. This avoids applying a formatting function to the column:

SELECT *
FROM test_timestamp
WHERE timestamp_v >= TIMESTAMP '2022-08-30 00:00:00'
  AND timestamp_v <  TIMESTAMP '2022-08-31 00:00:00';

 id |     timestamp_v     |     timestamptz_v
----+---------------------+------------------------
  2 | 2022-08-30 00:00:00 | 2022-08-30 00:00:00+08
  1 | 2022-08-30 11:11:11 | 2022-08-30 11:11:11+08
(2 rows)

You can also find all rows with timestamp_v is greater than 2022-08-30, as follows:

SELECT *
FROM test_timestamp
WHERE timestamp_v > '2022-08-30';

 id |     timestamp_v     |     timestamptz_v
----+---------------------+------------------------
  1 | 2022-08-30 11:11:11 | 2022-08-30 11:11:11+08
(1 row)

Format the time value in the specified format

PostgreSQL provides TO_CHAR() functions to output time values ​​in a specified format. The TO_CHAR() function accepts two parameters, the first parameter is the time value to format, and the second parameter is the format.

To display the time in the yyyy/mm/dd format, use the following statement:

SELECT
  id,
  to_char(timestamp_v, 'YYYY/MM/DD HH24:MI:SS'),
  to_char(timestamptz_v, 'YYYY/MM/DD HH24:MI:SS TZH')
FROM
  test_timestamp;

 id |       to_char       |         to_char
----+---------------------+-------------------------
  2 | 2022/08/30 00:00:00 | 2022/08/30 00:00:00 +08
  1 | 2022/08/30 11:11:11 | 2022/08/30 11:11:11 +08
(2 rows)

PostgreSQL timestamp functions

To efficiently process timestamped data, PostgreSQL provides some convenience functions.

To get the current timestamp, use now() or current_timestamp, as follows:

SELECT now(), current_timestamp;

             now              |      current_timestamp
------------------------------+------------------------------
 2022-09-02 10:14:14.06204+08 | 2022-09-02 10:14:14.06204+08

In addition to this, you can use transaction_timestamp(), statement_timestamp(), localtimestamp(), or clock_timestamp() to get the current timestamp.

You can specify the fractional second precision of the timestamp using current_timestamp, as follows:

SELECT current_timestamp, current_timestamp(2);

       current_timestamp       |     current_timestamp
-------------------------------+---------------------------
 2022-09-02 10:15:22.670007+08 | 2022-09-02 10:15:22.67+08

To get the year, month, day, hour, minute, and second values ​​in a timestamp value, use the date_part() function :

SELECT
  date_part('year', now()) "year",
  date_part('month', now()) "month",
  date_part('day', now()) "day",
  date_part('hour', now()) "hour",
  date_part('minute', now()) "minute",
  date_part('second', now()) "second";

 year | month | day | hour | minute |  second
------+-------+-----+------+--------+-----------
 2022 |     9 |   2 |   10 |     17 | 14.520472

Conclusion

PostgreSQL supports TIMESTAMP and TIMESTAMPTZ to store timestamp values. TIMESTAMP is used to store timestamps without time zone, and TIMESTAMPTZ is used to store timestamps with time zone.