Timezones in Software: Why They're Hard and How to Handle Them

Every developer hits the timezone wall eventually. The calendar event that shows up an hour early. The 'yesterday's report' that includes today's data. The birthday reminder that fires at 2am for users in Tokyo. Timezone bugs are rarely caught in testing because developers test in their own timezone.

The Rules (Follow These Without Exception)

• Store all timestamps in UTC — no exceptions
• Use Unix timestamps (milliseconds) for storage and API communication
• Convert to local time only at display time
• Never use system local time in server code (server timezone ≠ user timezone)
• Use IANA timezone names ('America/New_York'), not abbreviations ('EST' — ambiguous)
• Let timezone libraries handle DST — don't implement DST logic yourself

Common Timezone Bugs

The 'off by one day' bug: a user creates an event at 11pm on January 5th in New York. In UTC, that's 4am January 6th. If you query for 'events on January 5th' using UTC midnight boundaries, you miss the event. Fix: query for events in the user's timezone, or use timezone-aware date boundaries.

The server timezone bug: your Node.js server runs in UTC. Your PostgreSQL database runs in UTC. Your development Mac runs in Pacific time. When you write new Date().toLocaleDateString() in server code, it uses the server's timezone — UTC, not the user's. Server-side code must never use local time functions.

The Database Column Type

PostgreSQL has TIMESTAMP and TIMESTAMPTZ (timestamp with time zone). Always use TIMESTAMPTZ — it stores everything in UTC and handles DST-aware conversions. TIMESTAMP stores exactly what you give it without timezone information, which sounds flexible but creates ambiguity. MySQL: DATETIME stores without timezone; TIMESTAMP stores in UTC and converts. Use TIMESTAMP (UTC) in MySQL.