Unix Timestamps in Databases

PostgreSQL — the most capable option

PostgreSQL offers the best timestamp support of any open-source database. Use TIMESTAMPTZ (timestamp with time zone) for all date-time storage.

-- Store: insert with any timezone, stored as UTC
INSERT INTO events (name, created_at) VALUES ('Launch', '2025-01-01 01:00:00 Europe/Paris');

-- Convert timestamp to Unix epoch (seconds)
SELECT EXTRACT(EPOCH FROM created_at)::bigint FROM events;

-- Convert Unix timestamp to timestamptz
SELECT to_timestamp(1735689600);               -- → 2025-01-01 00:00:00+00

-- Query a date range (timezone-aware)
SELECT * FROM events
WHERE created_at BETWEEN '2025-01-01T00:00:00Z' AND '2025-12-31T23:59:59Z';

-- Store as raw integer (alternative for portability)
ALTER TABLE events ADD COLUMN ts_unix BIGINT;
UPDATE events SET ts_unix = EXTRACT(EPOCH FROM created_at)::bigint;

-- Query by integer timestamp
SELECT * FROM events WHERE ts_unix >= 1735689600 AND ts_unix < 1767225600;

MySQL — watch out for TIMESTAMP vs DATETIME

MySQL has two datetime types with critically different behaviour:

• TIMESTAMP — stored as UTC internally, auto-converts with the session timezone on read. Range: 1970-01-01 00:00:01 to 2038-01-19 03:14:07 UTC. Limited by the 32-bit Unix timestamp.
• DATETIME — stored as-is, no timezone conversion. Range: 1000-01-01 to 9999-12-31. Use this if you need dates past 2038 or before 1970.

-- Get Unix timestamp from a DATETIME column
SELECT UNIX_TIMESTAMP(created_at) FROM events;

-- Convert Unix timestamp to DATETIME
SELECT FROM_UNIXTIME(1735689600);          -- → 2025-01-01 00:00:00 (server tz)
SELECT FROM_UNIXTIME(1735689600, '%Y-%m-%d %H:%i:%s');

-- Store as BIGINT for future-proof Unix timestamps
ALTER TABLE events ADD COLUMN ts_unix BIGINT UNSIGNED;
INSERT INTO events (ts_unix) VALUES (1735689600);

-- Query range with BIGINT
SELECT * FROM events WHERE ts_unix BETWEEN 1735689600 AND 1767225600;

-- Avoid TIMESTAMP columns for dates after 2038
-- Use DATETIME(3) for millisecond precision
ALTER TABLE events MODIFY created_at DATETIME(3);

SQLite — everything is flexible (sometimes too much)

SQLite has no dedicated datetime type. Dates are stored as TEXT (ISO 8601 strings), REAL (Julian day numbers), or INTEGER (Unix timestamps). The built-in date functions handle all three.

-- Store as integer (recommended for Unix timestamps)
CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  name TEXT,
  created_at INTEGER  -- Unix timestamp in seconds
);

INSERT INTO events (name, created_at) VALUES ('Launch', 1735689600);

-- Convert integer to formatted date
SELECT datetime(created_at, 'unixepoch') FROM events;
-- → 2025-01-01 00:00:00

SELECT datetime(created_at, 'unixepoch', 'localtime') FROM events;
-- → local time

-- Current Unix timestamp
SELECT strftime('%s', 'now');  -- → seconds since epoch as text
SELECT CAST(strftime('%s', 'now') AS INTEGER);  -- as integer

-- Query range
SELECT * FROM events
WHERE created_at >= strftime('%s', '2025-01-01')
  AND created_at <  strftime('%s', '2026-01-01');

MongoDB — Date objects and Unix timestamps

// Store as Date object (recommended)
db.events.insertOne({ name: 'Launch', createdAt: new Date('2025-01-01T00:00:00Z') });

// Store as Unix timestamp integer (for external interop)
db.events.insertOne({ name: 'Launch', ts: 1735689600 });

// Convert Date to Unix timestamp in aggregation
db.events.aggregate([
  { $project: { ts: { $toLong: { $divide: [{ $toLong: '$createdAt' }, 1000] } } } }
]);

// Query by date range
db.events.find({
  createdAt: {
    $gte: new Date('2025-01-01T00:00:00Z'),
    $lt:  new Date('2026-01-01T00:00:00Z')
  }
});

General best practices

• Always use UTC in the database. Configure your database server to UTC and store all times in UTC. Apply user timezone only at the application layer.
• Prefer TIMESTAMPTZ over TIMESTAMP in PostgreSQL. The timezone-aware type prevents accidental display of wrong local times.
• Avoid MySQL TIMESTAMP for new projects. Use DATETIME or BIGINT to avoid the 2038 limit.
• Index timestamp columns. Date range queries are extremely common. A B-tree index on a Unix timestamp integer is as efficient as on a native datetime type.
• Document the unit. Name integer columns created_at_s or expires_at_ms to make the unit explicit.
• Use BIGINT if unsure. A signed 64-bit integer covers from −9 quintillion to +9 quintillion seconds — effectively unlimited.