PostgreSQL TIMESTAMP WITH TIME ZONE Data Type

Summary: in this tutorial, you’ll learn how to use the PostgreSQL TIMESTAMP WITH TIME ZONE data type to handle timestamp values with time zone.

Getting Started with the PostgreSQL TIMESTAMP WITH TIME ZONE data type #

In PostgreSQL, you can use the TIMESTAMP WITH TIME ZONE data type to store time zone-aware timestamp values.

Here’s the syntax for defining a column with the type TIMESTAMP WITH TIME ZONE:

CREATE TABLE table_name(
    column_name TIMESTAMP WITH TIME ZONE,
    ...
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

TIMESTAMPTZ is the shorthand of the TIMESTAMP WITH TIME ZONE, so you can use it to save some typing:

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

When you insert a timestamp into a TIMESTAMPTZ column, PostgreSQL converts it to UTC for storage. This conversion ensures the stored timestamp is consistent and not affected by time zone differences.

When you retrieve a timestamp from a TIMESTAMPTZ column, PostgreSQL converts the stored UTC timestamp back to the time zone of your session. This conversion means that the time you receive is adjusted to the time zone set by your database session.

It’s important to note that PostgreSQL does not store time zones in the TIMESTAMPTZ column but does the time zone conversion when stored and retrieved.

You can set the time zone of your database session using the SET TIME ZONE statement. For example:

SET TIME ZONE 'America/New_York';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The TIMESTAMPTZ can automatically handle daylight saving time changes and other time zone-related adjustments.

In practice, you’ll often use the TIMESTAMP WITH TIME ZONE data type in applications that handle time across time zones, such as scheduling apps, international transactions, and logging events.

Getting the current timestamp with a time zone #

The CURRENT_TIMESTAMP function returns the current time with a time zone.

SELECT CURRENT_TIMESTAMP;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

       current_timestamp
-------------------------------
 2024-12-08 02:42:59.773965-08Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

It shows the current time in the 'US/Pacific' time zone.

To set the time zone of your database session to a different one, you use the SET TIME ZONE statement:

SET TIME ZONE 'America/New_York';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If you get the current timestamp again, you’ll see the time is adjusted to the ‘America/New_York’ time zone:

SELECT CURRENT_TIMESTAMP;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

       current_timestamp
-------------------------------
 2024-12-08 05:43:11.587639-05Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To get a list of supported time zone names, you use the following statement:

SELECT
  name,
  abbrev,
  utc_offset,
  is_dst
FROM
  pg_timezone_names;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Setting a default value for a TIMESTAMPTZ column #

To set a default value for a TIMESTAMPTZ column, you can use a DEFAULT constraint:

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

In this example, if you insert or update the TIMESTAMPTZ column without providing a value, PostgreSQL will use the current timestamp returned by the CURRENT_TIMESTAMP function for inserting or updating.

PostgreSQL TIMESTAMPTZ data type example #

First, create a table called inventory_scans that tracks scanned products in the inventory:

CREATE TABLE inventory_scans (
  scan_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  warehouse_id INT NOT NULL,
  product_id INT NOT NULL,
  serial_no VARCHAR(25) NOT NULL,
  quantity INT NOT NULL,
  scanned_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, insert some rows into the inventory_scans table:

INSERT INTO
  inventory_scans (warehouse_id, product_id, serial_no, quantity)
VALUES
  (1, 1, 'F2LZK3H8N72Q', 1),
  (2, 1, 'R58N40Y1A2B3', 1) 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 scan_id | warehouse_id | product_id |  serial_no   | quantity |          scanned_at
---------+--------------+------------+--------------+----------+-------------------------------
       1 |            1 |          1 | F2LZK3H8N72Q |        1 | 2024-12-08 05:44:11.998853-05
       2 |            2 |          1 | R58N40Y1A2B3 |        1 | 2024-12-08 05:44:11.998853-05

Summary #

• Use the PostgreSQL TIMESTAMP WITH TIME ZONE data type (or TIMESTAMPTZ) to handle timestamp values with time zone.
• Use the CURRENT_TIMESTAMP to get the current timestamp with a time zone.

Quiz #