SQL Timezones: Storing Time Safely

Storing dates and times in a database seems simple until you have users in different countries. Mismanaging timezones is one of the most common causes of data corruption and logic errors in modern web applications.

The Golden Rule: Store in UTC

Almost all database architects agree: Always store absolute timestamps in UTC (Coordinated Universal Time).

By normalizing your data to a single reference point at the storage layer, you avoid "double-converting" times and making mistakes during database migrations. You should only convert the time to a local timezone at the presentation layer (when it’s shown to the user).

PostgreSQL: `TIMESTAMP WITH TIME ZONE`

In PostgreSQL, the `TIMESTAMPTZ` type is the industry standard. Despite its name, it doesn't actually store the timezone offset in the field. Instead, it converts any input to UTC and stores that. When you query it, PostgreSQL converts it back to the timezone of the current database session.

MySQL: `DATETIME` vs. `TIMESTAMP`

In MySQL, the choices are more confusing:

• DATETIME: Stores the date and time exactly as provided. It does not change based on the server's timezone settings. Use this for "wall clock" times (like a store's opening hour).
• TIMESTAMP: Converts the input from the session timezone to UTC for storage, then back for retrieval. It has a range limit (ending in 2038).

Common Pitfalls

• Server Time: Never rely on `NOW()` if your database server is set to a local timezone (like EST). Set your database server itself to UTC.
• Leap Seconds: Databases generally follow the POSIX standard, which ignores leap seconds. A minute in SQL is always exactly 60 seconds.
• Daylight Saving: Since UTC has no daylight saving time, storing in UTC automatically solves the problem of "missing" or "overlapping" hours during DST transitions.

Conclusion

Database time management is about predictability. By treating your database as a UTC-only zone, you ensure that your data remains valid even as your application scales across the globe. On the Epoch Clock, you see time the way a database sees it: as a single, unwavering count of seconds.