PostgreSQL TIMESTAMP Data Type

Summary: in this tutorial, you’ll learn how to use the PostgreSQL TIMESTAMP data type to store date and time values in the database.

Getting Started with the PostgreSQL TIMESTAMP Data Type #

In PostgreSQL, the TIMESTAMP data type allows you to store both date and time in the database. The TIMESTAMP data type does not include any time zone data.

It means that when you change the time zone of your PostgreSQL server, the TIMESTAMP values stored in the database won’t automatically change.

If you want to store date and time with a time zone, you can use the TIMESTAMPTZ data type instead.

PostgreSQL uses 8 bytes to store a TIMESTAMP value. The valid range of the TIMESTAMP is from 4713 BC to 294276 AD.

Here’s the syntax for defining a TIMESTAMP column:

CREATE TABLE table_name(
    column_name TIMESTAMP
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If you want to set a default value for a TIMESTAMP column, you can apply a DEFAULT constraint with the default value returned by the LOCALTIMESTAMP function:

CREATE TABLE table_name(
    column_name TIMESTAMP DEFAULT LOCALTIMESTAMP
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Note that the LOCALTIMESTAMP returns the local date and time based on the local time of the PostgreSQL server.

PostgreSQL uses the ISO 8601 standard for both input and output timestamps:

yyyy-mm-dd hh:mm:ssCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

ISO 8601 uses the upper letter T to separate the date and time:

yyyy-mm-dd<strong>T</strong>hh:mm:ssCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL accepts this format for input. But it uses space rather than the uppercase letter T for readability.

yyyy-mm-dd hh:mm:ssCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Additionally, PostgreSQL supports other reasonable timestamp formats that are not covered in this tutorial.

In practice, you should use the TIMESTAMP data type to store timestamps only when you save and retrieve them from the database and you’re not doing any calculations. Otherwise, you should use the TIMESTAMPTZ instead.

PostgreSQL TIMESTAMP Data Type example #

The TIMESTAMP data type is suitable for storing local time without caring about the time zone.

For example, you can use the TIMESTAMP data type to store local time like local theater shows. It doesn’t matter in which time zone.

First, create a table called events, which stores local events like theater shows.

CREATE TABLE events (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  location VARCHAR(255) NOT NULL,
  start_at TIMESTAMP NOT NULL,
  end_at TIMESTAMP NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, insert some rows into the events table:

INSERT INTO
  events (title, location, start_at, end_at)
VALUES
  (
    '"O" by Cirque du Soleil',
    'O Theatre, Bellagio',
    '2024-12-08 19:00:00',
    '2024-12-08 21:30:00'
  ),
  (
    'David Copperfield',
    'David Copperfield Theater, MGM Grand',
    '2024-12-07 16:00:00',
    '2024-12-07 19:00:00'
  ),
  (
    'Awakening',
    'Awakening Theater, Wynn Las Vegas',
    '2024-12-06 19:00:00',
    '2024-12-06 21:00:00'
  ) 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 id |          title          |               location               |      start_at       |       end_at
----+-------------------------+--------------------------------------+---------------------+---------------------
  1 | "O" by Cirque du Soleil | O Theatre, Bellagio                  | 2024-12-08 19:00:00 | 2024-12-08 21:30:00
  2 | David Copperfield       | David Copperfield Theater, MGM Grand | 2024-12-07 16:00:00 | 2024-12-07 19:00:00
  3 | Awakening               | Awakening Theater, Wynn Las Vegas    | 2024-12-06 19:00:00 | 2024-12-06 21:00:00Code language: JavaScript (javascript)

Third, calculate the duration of each event by subtracting the start time from the end time:

SELECT
  title,
  end_at - start_at duration
FROM
  events;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

          title          | duration
-------------------------+----------
 "O" by Cirque du Soleil | 02:30:00
 David Copperfield       | 03:00:00
 Awakening               | 02:00:00Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the PostgreSQL TIMESTAMP data type to store date and time values without time zone.

Quiz #