Gotcha: PostgreSQL timestamp with and without timezone

Inconsistent datetime handling when mixing TIMESTAMP and TIMESTAMPTZ columns, or when server timezone changes.

Always use TIMESTAMPTZ:

-- TIMESTAMP (without timezone) - stores literal value
-- 2024-01-15 10:00:00 is stored as-is
-- Interpreted differently depending on session timezone!

-- TIMESTAMPTZ (with timezone) - stores UTC internally
-- 2024-01-15 10:00:00-05 is stored as 2024-01-15 15:00:00 UTC
-- Displayed correctly in any timezone

-- Always use TIMESTAMPTZ
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name TEXT,
    starts_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    -- NOT: starts_at TIMESTAMP
);

-- Set session timezone for display
SET timezone = 'America/New_York';

-- How TIMESTAMP goes wrong:
SET timezone = 'UTC';
INSERT INTO bad_table (ts) VALUES ('2024-01-15 10:00:00');
-- Stored as: 2024-01-15 10:00:00

SET timezone = 'America/New_York';
SELECT ts FROM bad_table;
-- Returns: 2024-01-15 10:00:00 (WRONG! Should be 05:00:00 EST)

-- Application code: always send UTC
-- Python: datetime.utcnow() or datetime.now(timezone.utc)
-- JavaScript: new Date().toISOString()

TIMESTAMP stores the literal time value without timezone context. When the server or session timezone changes, the same stored value is interpreted differently, causing subtle bugs.

PostgreSQL databases storing date/time values