Common Time Zone Bugs in Databases and Server Logs

A surprising share of "the numbers don't match" bugs in backend systems trace back to how a database or a log line stores a moment in time — not to any actual data-processing error. Two habits prevent almost all of them: know which timestamp type your database column actually is, and never write a timestamp to a log without an explicit offset.

TIMESTAMP vs. TIMESTAMPTZ — They're Not Interchangeable

In PostgreSQL, a plain TIMESTAMP column stores a date and time with no time zone information — it's the database equivalent of a "naive" datetime in application code. A TIMESTAMPTZ column, despite what the name suggests, doesn't actually store a time zone either — it stores everything internally as UTC and converts to and from the session's configured time zone on the way in and out. The practical difference: insert a value into TIMESTAMPTZ from a session in one time zone and read it back from a session in a different time zone, and you'll see the same instant displayed as a different local time — correctly. Do the same with a plain TIMESTAMP column and the raw value never adjusts, because there was never any zone attached to convert from in the first place. MySQL has a similar split between DATETIME (no zone, no conversion) and TIMESTAMP (UTC-based, timezone-adjusted on read/write, but with a narrower valid date range — it can't represent dates after January 2038, the same Year 2038 problem covered on our Unix Timestamp Converter page).

The Practical Rule: Use the Timezone-Aware Type, Always

Unless there's a specific reason to store a genuinely zone-less value (a recurring local wall-clock event like "every day at 9am wherever the user is," where the zone should shift with the user rather than staying fixed), default to the timezone-aware column type and let the database handle the UTC conversion. Storing a naive value and trying to remember which zone it was written in in application code is exactly the ambiguity that causes bugs months later when a different engineer touches that table.

Log Timestamps: The Same Problem, Spread Across Servers

A log line without an explicit UTC offset is exactly as ambiguous as a database TIMESTAMP column, but the stakes are often higher, because logs from multiple servers frequently get merged and compared. If one server logs in its local time zone and another logs in UTC, correlating an event across both — "which service touched this request first?" — becomes guesswork the moment those servers aren't in the same zone, or one of them observes daylight saving and the other doesn't. Configuring every server and every logging library to emit RFC 3339 timestamps in UTC (see our ISO 8601 vs. RFC 3339 vs. Unix Timestamp guide) removes the ambiguity entirely and makes merged, multi-server log timelines trustworthy.

"Today" Means Different Things on Different Servers

A batch job that groups records by "today's date" is implicitly choosing a time zone boundary for midnight, whether or not that choice was deliberate. A server using its local system time zone for that grouping will disagree with a server in a different region about which records belong to "today" for several hours around each midnight — a classic source of off-by-one-day bugs in daily reports and reconciliation jobs that span multiple regions. Defining "day" against a single, explicit reference zone (usually UTC, or the business's own official reporting zone) for every server in the pipeline avoids the disagreement — see our Cron Jobs and Time Zones guide for the same principle applied to scheduling.

A Quick Diagnostic Checklist

• Does every TIMESTAMP-family column in your schema either carry an explicit zone, or have a clear, documented convention for which zone naive values are written in?
• Do all your servers and services log in the same zone (ideally UTC), regardless of where they're physically or geographically deployed?
• Does any batch or reporting job group records by calendar date using a time zone that isn't explicitly chosen and documented?
• Do date-only fields ("expires on 2026-08-01") ever get compared against full datetime values without first deciding what time that date actually means?

Related

To convert a raw timestamp value by hand while debugging, use our Unix Timestamp Converter. For the JavaScript-side equivalent of this problem, see Handling Timezones & UTC in JavaScript.