Working with Time Zones: TIMEZONE

Let’s say you’ve got an app for booking plane tickets. A flight leaves New York at 10:00 local time and lands in London at 22:00 local time. If you ignore time zones, your server can turn this into total chaos, showing the wrong arrival time.

Time zones are either your best friends (or your worst enemies when things go sideways). If your users are in different countries or you need to deal with schedules that depend on local time (like flight times or event schedules), then handling time zones becomes super important.

Types of Temporal Data

We’ve already talked about the two data types for working with timestamps:

• TIMESTAMP: date and time without time zone awareness.
• TIMESTAMPTZ: date and time with time zone awareness.

Let’s break them down again with an example.

-- Creating a table with two columns: TIMESTAMP and TIMESTAMPTZ
CREATE TABLE flight_schedule (
    flight_id SERIAL PRIMARY KEY,
    departure_time TIMESTAMP,
    departure_time_with_tz TIMESTAMPTZ
);

-- Inserting data
INSERT INTO flight_schedule (departure_time, departure_time_with_tz)
VALUES
    ('2023-10-25 10:00:00', '2023-10-25 10:00:00+00');

-- Checking the data
SELECT * FROM flight_schedule;

The result will depend on your server’s time zone. For example:

The key difference:

• The departure_time column just stores the date and time with no time zone attached.
• The departure_time_with_tz column stores the date and time along with time zone info (+00 in this case).

Converting Time Between Time Zones

To work with time zones in PostgreSQL, you use the AT TIME ZONE function.

Converting UTC to Local Time

Let’s say you have a timestamp in UTC (Coordinated Universal Time). You want to show it to a user who’s in the America/New_York time zone.

SELECT
    '2023-10-25 14:00:00+00'::TIMESTAMPTZ AT TIME ZONE 'America/New_York' AS local_time;

Result:

AT TIME ZONE here works like magic: it converts the time from UTC to the specified time zone.

Converting Local Time to UTC

Now let’s flip it: you have a time in America/New_York, and you want to convert it to UTC.

SELECT
    '2023-10-25 10:00:00'::TIMESTAMP AT TIME ZONE 'America/New_York' AS utc_time;

Result:

Notice that the result will be in TIMESTAMPTZ format, since it includes time zone info (UTC in this case).

Working with the TIMESTAMPTZ Data Type

When you’re working with TIMESTAMPTZ, PostgreSQL automatically takes your server’s time zone (or the one you set) into account.

You can set the time zone for your current session using:

SET TIMEZONE = 'Europe/Istanbul';

After that, all operations with TIMESTAMPTZ will use this time zone.

Example: inserting and selecting data

-- Set the time zone
SET TIMEZONE = 'Europe/Istanbul';

-- Insert data
INSERT INTO flight_schedule (departure_time_with_tz)
VALUES ('2023-10-25 10:00:00+00');

-- Check the data
SELECT departure_time_with_tz FROM flight_schedule;

Result in the Europe/Istanbul time zone:

PostgreSQL automatically converts the time from UTC using the time zone you specified.

Practical Examples

Handling time zones for schedules. Let’s say we have a table with flight schedules, where each record stores the departure time in UTC. We want to show the departure time for each flight in local time.

SELECT
    flight_id,
    departure_time_with_tz AT TIME ZONE 'America/New_York' AS local_time
FROM flight_schedule;

Comparing time data from different time zones. Imagine we’re comparing two events that happened in different cities. PostgreSQL lets you do this by automatically converting the data to a single time zone.

SELECT
    '2023-10-25 10:00:00+03'::TIMESTAMPTZ > '2023-10-25 07:00:00+00'::TIMESTAMPTZ AS event_one_later;

Result:

The comparison returned true, because 10:00+03 is the same as 07:00+00.

Tips and Common Pitfalls

Working with time is tricky. Here’s what often goes wrong:

• People use TIMESTAMP instead of TIMESTAMPTZ, and then wonder why the times don’t match up — it’s because time zones are just ignored.
• They don’t know what time zone the server is using, so data gets inserted in one time and read in another.
• They mess up the time zone name when using AT TIME ZONE — and get an error or the wrong time.

To avoid these headaches:

• Almost always use TIMESTAMPTZ, especially if your data might depend on time zones.
• Store time in UTC, and only convert to the user’s time zone when displaying it.
• If you want to dig deeper, here’s the official PostgreSQL docs on date/time and time zones — super useful stuff.