PostgreSQL TIMESTAMP Data Type

This article describes how to use the timestamp type in PostgreSQL and some useful functions for working with timestamp data.

The PostgreSQL timestamp type is a data type used to store a combination of date and time. PostgreSQL supports two types of timestamp data type, including TIMESTAMP and TIMESTAMPTZ:

TIMESTAMP is written in full as TIMESTAMP WITHOUT TIME ZONE, used to store timestamps without a time zone.
TIMESTAMPTZ is written in full as TIMESTAMP WITH TIME ZONE, used to store timestamps with a time zone.

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

Internally in PostgreSQL, the value of the TIMESTAMPTZ type is stored as its corresponding UTC value. When you query a TIMESTAMPTZ value, PostgreSQL converts the UTC value back to the time value in the time zone set by the database server, user, or current database connection.

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

The Timestamp data type uses the format YYYY-MM-DD HH:MI:SS[.ssssss] to store values.

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:

The id column is the primary key column that identifies each row, and it is an identity column.
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.
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)

Although we did not specify a value with a time zone for the TIMESTAMPTZ column, PostgreSQL automatically adds time zone information to the value of this column.

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 with date of timestamp_v is 2022-08-30, using the following statement:

SELECT *
FROM test_timestamp
WHERE to_char(timestamp_v, 'YYYY-MM-DD') = '2022-08-30';

 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.