PostgreSQL应用中多配送中心跨时区问题的技术问询

Hey there! Let's walk through the ideal approach for handling your cross-timezone event storage in PostgreSQL—this is a super common scenario for distributed warehouse systems, so we’ve got battle-tested patterns to make this smooth.

The golden rule for cross-timezone systems is: always store time in UTC as your single source of truth. This eliminates ambiguity and ensures all events are aligned, regardless of where your warehouses are located. User-facing times can be converted to local time on the fly, but the underlying data stays consistent.

You’ll need two key components: a table for your warehouses (with timezone info) and a table for events (with timezone-aware timestamps).

Warehouse Table #

Add a timezone column to store the IANA standard timezone name for each warehouse (e.g., 'America/New_York', 'Asia/Tokyo'). Avoid using abbreviations like EST—they’re inconsistent and don’t account for daylight saving time.

CREATE TABLE warehouses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    timezone TEXT NOT NULL DEFAULT 'UTC', -- Use IANA timezone names
    address TEXT
);

Events Table #

Use TIMESTAMPTZ (timestamp with time zone) for your event time. PostgreSQL stores this internally as UTC, but it can automatically convert to/from other timezones when needed.

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    warehouse_id INT REFERENCES warehouses(id) NOT NULL,
    event_name VARCHAR(100) NOT NULL,
    event_time TIMESTAMPTZ NOT NULL, -- Stores UTC internally
    description TEXT
);

When a user creates an event for a specific warehouse, you need to convert their local time input to UTC before storing it. You can do this either in your application layer or directly in PostgreSQL.

Option 1: Convert in Application Layer #

Take the user’s local time (e.g., 2024-05-20 14:30 for New York) and the warehouse’s timezone (America/New_York), convert to UTC, then insert:

INSERT INTO events (warehouse_id, event_name, event_time)
VALUES (1, 'Inventory Audit', '2024-05-20 18:30:00+00'); -- UTC equivalent

Option 2: Convert Directly in PostgreSQL #

If your app passes the local time and warehouse timezone, use the AT TIME ZONE function to handle conversion:

INSERT INTO events (warehouse_id, event_name, event_time)
VALUES (
    1,
    'Inventory Audit',
    '2024-05-20 14:30:00' AT TIME ZONE 'America/New_York'
);

This converts the local time to a TIMESTAMPTZ (UTC) value automatically.

When retrieving events for a warehouse, convert the stored UTC time back to the warehouse’s local time using AT TIME ZONE:

SELECT
    e.event_name,
    e.event_time AT TIME ZONE w.timezone AS local_event_time, -- Converts to warehouse's local time
    e.description,
    w.name AS warehouse_name
FROM events e
JOIN warehouses w ON e.warehouse_id = w.id
WHERE w.id = 1; -- Filter for a specific warehouse

The local_event_time column will return the event time in the warehouse’s timezone, formatted as a TIMESTAMP (no timezone attached, since it’s now a local time).

Since we’re using IANA timezone names, PostgreSQL automatically handles daylight saving time transitions. For example, if a warehouse is in New York, queries will adjust between EST and EDT without any extra work from you—no manual offset updates needed.

• Don’t use TIMESTAMP (without time zone): This stores a naive time with no timezone context, leading to ambiguity (is 14:30 New York time or Tokyo time?).
• Don’t use timezone abbreviations: Abbreviations like PST or CET can be ambiguous (some regions share the same abbreviation) and don’t account for DST. Stick to IANA names (you can view all valid options with SELECT * FROM pg_timezone_names;).
• Don’t rely on session timezone: Avoid setting SET TIME ZONE for your sessions—this can lead to inconsistent results across different users. Always explicitly convert using the warehouse’s stored timezone.

This approach keeps your data consistent, handles all timezone edge cases (like DST), and makes it easy to display events in the correct local time for each warehouse. It’s scalable too—add new warehouses in any timezone without reworking your core logic.

内容的提问来源于stack exchange，提问作者Luiz