Handling Time Zones Correctly in SQL: UTC, Offsets, and DST

1. TIMESTAMP WITH TIME ZONE vs. WITHOUT TIME ZONE: the Technical Difference

TIMESTAMP WITHOUT TIME ZONE stores only a bare calendar value, such as 2026-06-15 14:30:00, with no information at all about which time zone that value is meant in. Two applications can interpret the exact same value completely differently if they make different assumptions about the intended time zone, and the database itself has no way of resolving that ambiguity.

TIMESTAMP WITH TIME ZONE, known as timestamptz in PostgreSQL, solves the problem by having the database immediately convert an incoming value to UTC on insert and store only UTC internally. On a query, the database converts the value back into the current session's time zone. The name is a bit misleading here, since no time zone information itself is stored, only a UTC point in time whose display depends on the session's time zone.

-- PostgreSQL: input in session time zone, stored internally as UTC
SET timezone = 'Europe/Berlin';
INSERT INTO appointment (id, starts_at) VALUES (1, '2026-06-15 14:30:00');

-- The same row read from a session with a different time zone
SET timezone = 'America/New_York';
SELECT starts_at FROM appointment WHERE id = 1;
-- returns 2026-06-15 08:30:00-04, the same UTC moment, different display

2. Pitfall 1: Storing Server Time Instead of UTC

If a naive timestamp type without any time zone information is used and the application simply writes the local server time into it, that appears to work fine at first, as long as the server and every user sit in the same time zone. As soon as a second application server in a different time zone is added, cross-continent database replication is set up, or the hosting provider changes, the stored values drift apart without a single SQL statement ever having been touched.

This mistake is especially treacherous during data exports and imports between systems: a CSV export from a database running on Europe/Berlin server time, imported into a system that assumes UTC, shifts every single timestamp by one or two hours, usually unnoticed, until someone finds an order that appears to have been processed before it was even created.

3. Pitfall 2: Daylight Saving Transitions in Recurring Events

When clocks move from standard to daylight saving time, a particular local time, such as 2:30 AM in Europe/Berlin, simply does not exist that day, because the clock jumps straight from 2:00 to 3:00 AM. When clocks move back from daylight saving to standard time, that same local time exists twice instead, once still in daylight saving time and once already in standard time. A naively stored recurring event scheduled at 2:30 AM can therefore be either impossible to interpret unambiguously or genuinely ambiguous on these two days each year.

This is not a rare edge case: any application with recurring events, such as nightly batch jobs or automated reports meant to run at a fixed local time, has to deal with this exact ambiguity twice a year whenever the scheduled time happens to fall into the affected window.

4. Pitfall 3: Using Fixed Offsets Instead of Named Time Zones

A fixed offset such as plus one hour only describes the difference from UTC on a specific day, not the rule governing when that offset changes. Europe/Berlin sits at plus one or plus two hours depending on the season, and only the named time zone identifier from the IANA time zone database actually knows the underlying transition rule. Anyone who stores just a fixed offset instead loses exactly that information and can no longer correctly reconstruct future transition dates.

For an already-past, one-off point in time, such as the timestamp of a log entry, a fixed UTC moment is entirely sufficient, because it will never change again. For a future, recurring appointment, however, such as every Monday at 9 AM local time, a fixed offset is not enough, because the daylight saving rule for that future date could theoretically still change and the intended local meaning needs to be preserved.

5. Database Specifics: PostgreSQL, MySQL, and SQL Server Compared

PostgreSQL offers timestamptz for consistent, always UTC-based storage with automatic conversion based on the session time zone. MySQL and MariaDB distinguish between TIMESTAMP, which is also stored internally in UTC and converted on read and write according to the session time zone, but is limited to the range from 1970 to 2038, and DATETIME, which stores a plain calendar value with no time zone conversion at all.

SQL Server offers datetimeoffset, a type that explicitly stores both the UTC point in time and the original offset, though without the name of a time zone, and datetime2, a plain, time-zone-less calendar value. Across all three systems, the rule holds: the time-zone-aware type handles UTC conversion automatically, while the plain type leaves that entirely to the application.

-- MySQL: TIMESTAMP converts automatically based on the session time zone
SET time_zone = '+02:00';
INSERT INTO log_entry (id, created_at) VALUES (1, NOW());

SET time_zone = '+00:00';
SELECT created_at FROM log_entry WHERE id = 1;
-- returns the same moment, displayed two hours earlier

6. Practical Recommendation: Always Store UTC, Apply the Time Zone Only at Display Time

The most robust ground rule is: the database stores UTC exclusively, either automatically through a time-zone-aware type such as timestamptz, or through a plain type whose values are consistently interpreted as UTC by discipline. Conversion into a user's own time zone happens only in the presentation layer, never inside the database and never in an intermediate processing layer.

For that conversion, the IANA time zone database with its named identifiers such as Europe/Berlin or America/New_York should always be used instead of fixed offsets, so daylight saving rules are applied automatically and correctly, and future legal changes to those rules, which have happened repeatedly in the past, can be rolled out centrally through a database time zone library update instead of an application code change.

7. Special Case: Storing Recurring Future Events Correctly

For a recurring future event, such as every Monday at 9 AM local time in Berlin, pre-computing UTC for the next occurrence is problematic, because the daylight saving rule between today and the actual event date could theoretically still change, and the user genuinely still means exactly 9 AM local time, not a fixed UTC moment. Here it is more robust to store the local time together with the time zone identifier and compute the actual UTC moment only shortly before the event is due.

For already-past, one-off events, the opposite holds: the UTC value computed once is final and should never be recomputed from a potentially changed time zone rule afterwards, since that would retroactively change the meaning of historical records.

8. Migration Strategy for Existing Naive Timestamp Columns

For an existing column with naive, time-zone-less values, the first step is to clarify which time zone was actually meant, usually the historical server time zone at the point the data was created, including whichever daylight saving rule applied at that time. Only after that clarification can a new, time-zone-aware column be populated correctly, typically through a one-time backfill script followed by switching every writing code path over to the new type.

During the transition period, it helps to maintain both columns in parallel and use automated consistency checks to confirm that the old and new columns actually describe the same moment, before the old, naive column is finally removed.

-- Migrating an existing naive column into a time-zone-aware column
ALTER TABLE appointment ADD COLUMN starts_at_utc TIMESTAMPTZ;

UPDATE appointment
SET starts_at_utc = starts_at AT TIME ZONE 'Europe/Berlin'
WHERE starts_at_utc IS NULL;

9. Uncovering Time Zone Bugs Through Deliberate Testing and Monitoring

Time zone bugs rarely show up in a standard test environment where every system is fixed to UTC. Effective tests deliberately set the session time zone to a range of different, sometimes wildly different values such as Pacific/Auckland, and specifically probe edge cases around midnight as well as the exact days daylight saving time starts and ends.

It also helps to run simple monitoring that regularly compares the database, application server, and operating system time zone against an expected value, since an unnoticed change to a server's time zone setting after an operating system update is, in practice, a surprisingly common root cause of suddenly shifted timestamps.

10. Summary

11. FAQ: Time Zones in SQL at a Glance