Veritabanında Zaman Depolama: UTC vs Yerel Saat

Always Store UTC in Your Database

The cardinal rule of database time storage: store all timestamps in UTC. This holds true regardless of where your users or servers are located. Local time in a database creates ambiguous data that breaks during daylight saving time transitions and makes multi-region deployments a nightmare.

Column Type Reference

PostgreSQL

-- TIMESTAMP WITH TIME ZONE (timestamptz) — recommended
-- Stores UTC internally, displays in session timezone
CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  scheduled_at TIMESTAMPTZ
);

-- TIMESTAMP WITHOUT TIME ZONE — avoid unless you understand the implications
-- No timezone conversion, stores exactly what you give it

MySQL / MariaDB

-- TIMESTAMP: stores UTC, range 1970-01-01 to 2038-01-19 (Y2K38 affected!)
-- DATETIME: no timezone conversion, range 1000-01-01 to 9999-12-31

-- Recommended: use DATETIME with explicit UTC handling in application
CREATE TABLE events (
  created_at DATETIME NOT NULL DEFAULT (UTC_TIMESTAMP())
);

-- Or store as BIGINT (Unix timestamp in milliseconds)
created_at_ms BIGINT NOT NULL

SQLite

-- SQLite has no native datetime type. Store as:
-- TEXT in ISO 8601 format: "2024-03-01T14:00:00Z"
-- REAL as Julian Day Number
-- INTEGER as Unix timestamp
CREATE TABLE events (
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

The DST Problem with Local Time Storage

Consider what happens when you store local New York time and DST ends in November. At 2:00 AM, clocks fall back to 1:00 AM. If you have events at 1:30 AM, you now have two events at 1:30 AM — which one came first? UTC avoids this completely because UTC time always moves forward.

Displaying Stored UTC to Users

-- PostgreSQL: convert to specific timezone at query time
SELECT
  created_at AT TIME ZONE 'Asia/Seoul' AS created_at_seoul
FROM events;

-- Or set session timezone
SET timezone = 'Asia/Seoul';
SELECT created_at FROM events;  -- auto-converted in output

Indexing Considerations

Date range queries on UTC-stored timestamps work correctly with standard B-tree indexes. If you frequently query by local date (e.g., "all events on March 1 in Seoul"), consider adding a generated column for the local date to avoid timezone-conversion in the index.

Migration: Local Time to UTC

-- PostgreSQL: convert existing local time column to UTC
UPDATE events
SET created_at = created_at AT TIME ZONE 'Asia/Seoul' AT TIME ZONE 'UTC'
WHERE ...;

-- Then change column type to TIMESTAMPTZ
ALTER TABLE events ALTER COLUMN created_at TYPE TIMESTAMPTZ;