What is timestamptz in PostgreSQL: Explanation and Usage

timestamptz in PostgreSQL is a data type that stores timestamps with time zone information. It automatically converts and stores the time in UTC internally but displays it according to the client's time zone settings. This helps manage date and time values consistently across different time zones.

How It Works

Think of timestamptz as a smart clock that always knows the exact moment in time no matter where you are in the world. When you save a timestamp with timestamptz, PostgreSQL converts it to a universal time standard called UTC (Coordinated Universal Time) behind the scenes.

When you later ask for that timestamp, PostgreSQL adjusts it to your local time zone automatically. This means you don't have to worry about manually converting times when users or systems are in different parts of the world.

It’s like having a watch that always shows the correct local time wherever you travel, while internally keeping track of the exact global time.

Example

This example shows how timestamptz stores and displays timestamps with time zone conversion.

sql

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  event_name TEXT,
  event_time TIMESTAMPTZ
);

INSERT INTO events (event_name, event_time) VALUES
('Meeting', '2024-06-01 15:00:00+02'),
('Webinar', '2024-06-01 10:00:00-04');

SELECT id, event_name, event_time FROM events;

Output

 id | event_name |       event_time        
----+------------+-------------------------
  1 | Meeting    | 2024-06-01 13:00:00+00
  2 | Webinar    | 2024-06-01 14:00:00+00

When to Use

Use timestamptz when you need to store exact moments in time that may be viewed or processed in different time zones. It is ideal for applications like scheduling, logging events, or tracking transactions across regions.

For example, a global calendar app uses timestamptz to ensure that an event scheduled in New York shows the correct local time when viewed by someone in London or Tokyo.

It helps avoid confusion and errors caused by manual time zone conversions or daylight saving changes.

Key Points

• timestamptz stores timestamps with time zone awareness.
• Internally, times are saved in UTC for consistency.
• Displayed times adjust automatically to the client or session time zone.
• Useful for global applications needing accurate time tracking.
• Prevents errors from manual time zone handling.

Key Takeaways

timestamptz stores timestamps with automatic time zone conversion.

It saves times internally in UTC to keep them consistent worldwide.

Displayed times adjust to the viewer's local time zone automatically.

Use it for applications that handle dates and times across multiple time zones.

It helps avoid mistakes from manual time zone calculations.