Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL TIMESTAMPTZ

PostgreSQL `TIMESTAMPTZ` is a data type that stores both date and time with time zone information. It is essential for applications that require precise timekeeping across different time zones.

Usage

The `TIMESTAMPTZ` type is used to store timestamp data that accounts for time zone differences, ensuring consistency in time-sensitive applications. It is often utilized in scenarios involving global data transactions and logging events. Internally, `TIMESTAMPTZ` stores all timestamps in UTC and converts them according to the client's time zone setting upon retrieval, ensuring consistency across different regions.

sql
CREATE TABLE event_logs (
    event_id SERIAL PRIMARY KEY,
    event_name VARCHAR(255),
    event_timestamp TIMESTAMPTZ
);

In this syntax, `event_timestamp TIMESTAMPTZ` stores the exact time of the event, including the time zone.

Examples

1. Basic TIMESTAMPTZ Insertion

sql
INSERT INTO event_logs (event_name, event_timestamp)
VALUES ('System Update', '2023-10-25 14:30:00+00');

This example inserts an event with a specific timestamp in UTC, including UTC time zone information.

2. Retrieving Current TIMESTAMPTZ

sql
SELECT NOW()::TIMESTAMPTZ AS current_time;

Here, the `NOW()` function fetches the current date and time, automatically adjusted to the server's time zone, and casts it to `TIMESTAMPTZ`.

3. Converting TIMESTAMPTZ to Another Time Zone

sql
SELECT event_name, 
       event_timestamp AT TIME ZONE 'America/New_York' AS event_time_est
FROM event_logs;

This example converts the stored `TIMESTAMPTZ` to Eastern Standard Time (EST) for display purposes, and does not alter the stored timestamp.

Tips and Best Practices

  • Use TIMESTAMPTZ for global applications. It ensures time consistency across different regions.
  • Be mindful of time zone conversions. Always specify the time zone when converting or comparing timestamps.
  • Leverage built-in functions. Use PostgreSQL's functions like `NOW()` or `CURRENT_TIMESTAMP` for dynamic timestamp retrieval.
  • Keep storage efficiency in mind. While `TIMESTAMPTZ` is powerful, ensure its necessity as it can consume more storage than `TIMESTAMP` without a time zone. Consider using `TIMESTAMP` without a time zone for localized applications where time zones are not a concern.
  • Set the PostgreSQL server's `timezone` configuration parameter correctly. This ensures the expected behavior of time zone conversions and accurate time retrieval.
  • Consider daylight saving time (DST) changes. `TIMESTAMPTZ` automatically adjusts for DST, which is crucial for applications sensitive to time changes. Ensure your server time zone settings handle DST appropriately.