CREATE TABLE events ( id SERIAL, local_start TIMESTAMPTZ, -- absolute moment in UTC user_time_zone TEXT -- 'America/Los_Angeles' ); | Feature | TIMESTAMP | TIMESTAMPTZ | |---------|-------------|----------------| | Time zone awareness | ❌ No | ✅ Yes (UTC internally) | | Changes with client time zone | ❌ No | ✅ Yes (on output) | | Safe for global apps | ❌ Risky | ✅ Safe | | Storage size | 8 bytes | 8 bytes (same!) |
-- Insert the same "local" value INSERT INTO time_test VALUES ('2025-04-14 14:00:00', '2025-04-14 14:00:00');
If you change your session time zone to 'Asia/Tokyo' (UTC+9) and read the table: postgres timestamp vs timestamptz
# Django/ORM example from django.utils import timezone import datetime bad_time = datetime.datetime(2025, 4, 14, 14, 0, 0) GOOD: Aware datetime good_time = timezone.now() # includes UTC offset
Chances are, you chose the wrong PostgreSQL temporal data type. CREATE TABLE events ( id SERIAL, local_start TIMESTAMPTZ,
Now, what is stored?
To preserve the user's original time zone (e.g., for compliance or display), you need a : CREATE TABLE events ( id SERIAL
-- Assume my session time zone is 'America/New_York' SET TIME ZONE 'America/New_York'; -- Create a test table CREATE TABLE time_test ( ts_native TIMESTAMP, -- without tz ts_tz TIMESTAMPTZ -- with tz );