Timezones in Databases — PostgreSQL, MySQL, and MongoDB

Handling Timezones in Databases

Different databases handle timezones in fundamentally different ways. Understanding these differences is crucial for data integrity.

PostgreSQL — The Gold Standard

PostgreSQL has the best timezone support of any major database.

TIMESTAMPTZ (recommended):

-- Stores internally as UTC, converts on input/output
CREATE TABLE events (
  starts_at TIMESTAMPTZ NOT NULL
);

-- Input: converted to UTC for storage
INSERT INTO events VALUES ('2024-03-15 10:30:00-05');

-- Output: converted to session timezone
SET timezone = 'Asia/Tokyo';
SELECT starts_at FROM events;
-- 2024-03-16 00:30:00+09

TIMESTAMP (without timezone):

-- Stores the literal value — no conversion, no timezone info
-- Use only for "wall clock time" that shouldn't change with timezone
CREATE TABLE daily_routines (
  wake_up_time TIMESTAMP NOT NULL  -- e.g., "07:00" regardless of timezone
);

MySQL

MySQL's timezone handling is less sophisticated:

-- DATETIME: stores literal value, no timezone conversion
-- TIMESTAMP: converts to UTC on storage, back to session tz on retrieval

CREATE TABLE events (
  -- TIMESTAMP is timezone-aware but limited to 2038-01-19
  starts_at TIMESTAMP NOT NULL,
  -- DATETIME has no size limit but is timezone-naive
  created_at DATETIME NOT NULL
);

-- Set session timezone
SET time_zone = 'America/New_York';

Important MySQL caveat: The TIMESTAMP type only supports dates through 2038-01-19 03:14:07 UTC (the Unix 32-bit overflow). For future dates, use DATETIME with explicit UTC handling.

MongoDB

MongoDB stores dates as UTC milliseconds since epoch:

// All dates are stored as UTC internally
db.events.insertOne({
  name: "Team Standup",
  startsAt: new ISODate("2024-03-15T14:30:00Z"),
  timezone: "America/New_York"  // Store separately for display
});

// Aggregation with timezone conversion
db.events.aggregate([
  {
    $project: {
      localTime: {
        $dateToString: {
          date: "$startsAt",
          timezone: "$timezone",
          format: "%Y-%m-%d %H:%M"
        }
      }
    }
  }
]);

Best Practices Across All Databases

1. Always use UTC for storage (TIMESTAMPTZ in PostgreSQL, UTC-converted DATETIME in MySQL)
2. Store the IANA timezone ID in a separate column when you need to display in local time
3. Set server and connection timezones to UTC to avoid surprises
4. Never rely on implicit timezone conversion — be explicit in every query