Unix Timestamps in Databases: MySQL, PostgreSQL, MongoDB, Redis

Different databases handle time storage differently. Here is a practical comparison of how each stores and queries Unix epoch timestamps, with ready-to-use query examples.

MySQL

MySQL has native UNIX_TIMESTAMP() and FROM_UNIXTIME() functions. Store timestamps as INT UNSIGNED for epoch seconds, or use the native DATETIME/TIMESTAMP types.

-- Get current Unix timestamp
SELECT UNIX_TIMESTAMP();

-- Convert epoch to readable date
SELECT FROM_UNIXTIME(1700000000);
-- Output: 2023-11-14 22:13:20

-- Convert date to epoch
SELECT UNIX_TIMESTAMP('2026-01-15 12:00:00');

-- Query rows within a time range
SELECT * FROM events
WHERE created_at BETWEEN UNIX_TIMESTAMP('2026-01-01') AND UNIX_TIMESTAMP('2026-02-01');

PostgreSQL

PostgreSQL uses EXTRACT(EPOCH FROM ...) and TO_TIMESTAMP(). It natively supports timezone-aware timestamps.

-- Get current Unix timestamp
SELECT EXTRACT(EPOCH FROM NOW())::BIGINT;

-- Convert epoch to timestamptz
SELECT TO_TIMESTAMP(1700000000);

-- Convert timestamptz to epoch
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2026-01-15 12:00:00 UTC')::BIGINT;

MongoDB

MongoDB stores dates as BSON Date (milliseconds). Use $toLong and $toDate in aggregation pipelines to work with epoch values.

// Insert with current timestamp (milliseconds)
db.events.insertOne({ created_at: new Date() });

// Query using epoch milliseconds
db.events.find({ created_at: { $gte: new Date(1700000000000) } });

// Aggregation: convert date to epoch ms
db.events.aggregate([
  { $project: { epoch_ms: { $toLong: "$created_at" } } }
]);

Redis

Redis has no native date type. Store timestamps as plain integers or sorted set scores, which is a common pattern for time-series data and expiring keys.

# Store a timestamp as a string
SET last_login:user123 1700000000

# Use sorted sets for time-ordered data (score = Unix timestamp)
ZADD events 1700000000 "user_signup"
ZADD events 1700000100 "user_purchase"

# Query events in a time range
ZRANGEBYSCORE events 1700000000 1700000200