MakeTimestamp logo
MakeTimestamp

Guides

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:
CREATE TABLE events (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  occurred_at_ms BIGINT GENERATED ALWAYS AS (UNIX_TIMESTAMP(occurred_at) * 1000) STORED,
  INDEX idx_events_occurred_at (occurred_at)
);

PostgreSQL

  • Preferred: TIMESTAMPTZ (stores UTC, renders with timezone).
  • Precision: up to microseconds; supports GENERATED ALWAYS columns for epochs.
CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  occurred_at_ms BIGINT GENERATED ALWAYS AS (EXTRACT(EPOCH FROM occurred_at) * 1000)::BIGINT STORED,
  INDEX (occurred_at)
);

SQLite

  • Stores as TEXT, REAL, or INTEGER; choose INTEGER epoch for consistency.
CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  occurred_at_ms INTEGER NOT NULL, -- store UTC milliseconds
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_events_occurred_at_ms ON events (occurred_at_ms);

Converting epoch ↔ human time

MySQL

-- epoch (seconds) -> timestamp
SELECT FROM_UNIXTIME(1704067200) AS utc_time;
-- timestamp -> epoch seconds/milliseconds
SELECT UNIX_TIMESTAMP(occurred_at) AS epoch_s,
       UNIX_TIMESTAMP(occurred_at) * 1000 AS epoch_ms
FROM events;
-- apply timezone safely
SELECT CONVERT_TZ(occurred_at, 'UTC', 'America/New_York') FROM events;

PostgreSQL

-- epoch seconds -> timestamptz (UTC)
SELECT to_timestamp(1704067200) AT TIME ZONE 'UTC';
-- timestamptz -> epoch seconds/milliseconds
SELECT EXTRACT(EPOCH FROM occurred_at) AS epoch_s,
       (EXTRACT(EPOCH FROM occurred_at) * 1000)::BIGINT AS epoch_ms
FROM events;
-- render in a specific zone
SELECT occurred_at AT TIME ZONE 'America/New_York' FROM events;

SQLite

-- epoch milliseconds -> UTC datetime string
SELECT datetime(occurred_at_ms / 1000, 'unixepoch') AS utc_time FROM events;
-- UTC datetime string -> epoch milliseconds
SELECT 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