Timestamps in Databases: Storage, Indexing, and Conversion Guide

Why this page

Timestamps drive almost every query: ordering events, filtering ranges, and joining logs. The catch is each database handles time differently. This guide gives you a fast decision tree, ready-to-ship table schemas, and conversions for MySQL, PostgreSQL, and SQLite—plus CTAs to the full converters when you need to debug data.

Quick decisions

• Always store in UTC; format to local only at the edge.
• Precision: seconds are fine for analytics; milliseconds (or more) for logs/traces.
• Type: use timezone-aware types when available; fall back to BIGINT epoch when interop is key.
• Indexes: avoid wrapping the column in functions; precompute derived columns if needed.
• Retention: partition or prune old data before indexes bloat.

Recommended types by engine

MySQL

• Preferred: TIMESTAMP (range 1970-2038) or DATETIME (wider range) stored as UTC.
• For higher precision or future dates beyond 2038: BIGINT epoch in milliseconds or microseconds.
• Table snippet:

Copy
SQL
1CREATE TABLE events (
2  id BIGINT PRIMARY KEY AUTO_INCREMENT,
3  occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
4  occurred_at_ms BIGINT GENERATED ALWAYS AS (UNIX_TIMESTAMP(occurred_at) * 1000) STORED,
5  INDEX idx_events_occurred_at (occurred_at)
6);

PostgreSQL

• Preferred: TIMESTAMPTZ (stores UTC, renders with timezone).
• Precision: up to microseconds; supports GENERATED ALWAYS columns for epochs.

Copy
SQL
1CREATE TABLE events (
2  id BIGSERIAL PRIMARY KEY,
3  occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
4  occurred_at_ms BIGINT GENERATED ALWAYS AS (EXTRACT(EPOCH FROM occurred_at) * 1000)::BIGINT STORED,
5  INDEX (occurred_at)
6);

SQLite

• Stores as TEXT, REAL, or INTEGER; choose INTEGER epoch for consistency.

Copy
SQL
1CREATE TABLE events (
2  id INTEGER PRIMARY KEY,
3  occurred_at_ms INTEGER NOT NULL, -- store UTC milliseconds
4  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
5);
6CREATE INDEX idx_events_occurred_at_ms ON events (occurred_at_ms);

Converting epoch ↔ human time

MySQL

Copy
SQL
1-- epoch (seconds) -> timestamp
2SELECT FROM_UNIXTIME(1704067200) AS utc_time;
3-- timestamp -> epoch seconds/milliseconds
4SELECT UNIX_TIMESTAMP(occurred_at) AS epoch_s,
5       UNIX_TIMESTAMP(occurred_at) * 1000 AS epoch_ms
6FROM events;
7-- apply timezone safely
8SELECT CONVERT_TZ(occurred_at, 'UTC', 'America/New_York') FROM events;

PostgreSQL

Copy
SQL
1-- epoch seconds -> timestamptz (UTC)
2SELECT to_timestamp(1704067200) AT TIME ZONE 'UTC';
3-- timestamptz -> epoch seconds/milliseconds
4SELECT EXTRACT(EPOCH FROM occurred_at) AS epoch_s,
5       (EXTRACT(EPOCH FROM occurred_at) * 1000)::BIGINT AS epoch_ms
6FROM events;
7-- render in a specific zone
8SELECT occurred_at AT TIME ZONE 'America/New_York' FROM events;

SQLite

Copy
SQL
1-- epoch milliseconds -> UTC datetime string
2SELECT datetime(occurred_at_ms / 1000, 'unixepoch') AS utc_time FROM events;
3-- UTC datetime string -> epoch milliseconds
4SELECT strftime('%s', '2024-12-01 00:00:00') * 1000 AS epoch_ms;

Need a precise converter while testing? Jump to Unix Timestamp Converter or Batch Timestamp Converter.

Indexing and query patterns

• Use range filters (WHERE occurred_at >= ... AND occurred_at < ...) to stay sargable.
• Avoid wrapping the column in DATE(), CAST(), or ::date in filters—use computed/generated columns if you need day-level groupings.
• For hot partitions, consider composite indexes (occurred_at, user_id) when filtering by both.
• Partitioning/retention: keep recent partitions small; archive old partitions to cheaper storage.
• Ordering: ORDER BY occurred_at DESC LIMIT 100 is index-friendly when the leading column is occurred_at.

Common pitfalls (and fixes)

• 2038 limit (MySQL TIMESTAMP): use DATETIME(3) or epoch BIGINT for future-dated rows.
• Session timezone drift: set time_zone='+00:00' (MySQL) or SET TIMEZONE='UTC'; (PostgreSQL) at connection startup.
• Mixed precision: never store seconds and milliseconds in the same column; enforce length on ingestion.
• Function-wrapped filters: WHERE DATE(occurred_at)=... disables indexes; precompute occurred_at_date instead.
• DST surprises in reports: store UTC; localize in the view layer with real IANA zones.

Migration checklist

1. Freeze writes or route through a dual-write layer.
2. Add a new UTC column (e.g., occurred_at_utc TIMESTAMPTZ or occurred_at_ms BIGINT).
3. Backfill with a deterministic conversion; verify with spot checks.
4. Add indexes on the new column, then switch reads to it.
5. Remove legacy column after monitoring and backfill verification.

FAQ

• Should I store as epoch or datetime? Epoch BIGINT is great for interop and precision; TIMESTAMPTZ is safer for built-in date math and readability.
• What precision should I choose? Logs/traces: ms or higher. Business data/analytics: seconds are usually enough.
• How do I stop users sending local time? Normalize at the edge: accept input with timezone, convert to UTC, and store only UTC.
• How do I validate inbound timestamps? Enforce digit-only payloads, length-based precision detection, and reasonable range (e.g., 2000-01-01 to 2100-01-01). See Timestamp Precision Levels.

Related tools and guides

• Unix Timestamp Converter
• Batch Timestamp Converter
• Timestamp Format Builder
• ISO 8601 Guide
• Timestamp Precision Levels
• How to Convert Timestamps in JavaScript