Storing Epoch Timestamps in SQL and NoSQL Databases

Epoch Timestamps in Databases

Choosing how to store timestamps in a database is a fundamental design decision. Unix epoch integers and native datetime types each have trade-offs.

PostgreSQL

-- Integer epoch (seconds)
CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  created_at BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())
);

-- Native timestamp
CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Converting between them
SELECT TO_TIMESTAMP(1705312200);  -- epoch → timestamp
SELECT EXTRACT(EPOCH FROM NOW()); -- timestamp → epoch

PostgreSQL's TIMESTAMPTZ is generally preferred because it handles timezone conversion automatically and supports rich date functions. Use BIGINT epoch storage when you need maximum portability or are interfacing with systems that expect epoch integers.

MySQL

-- Integer epoch
ALTER TABLE events ADD created_at BIGINT NOT NULL;

-- Native timestamp (stored as UTC internally)
ALTER TABLE events ADD created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- Conversions
SELECT FROM_UNIXTIME(1705312200);
SELECT UNIX_TIMESTAMP(NOW());

MySQL's TIMESTAMP type stores values as epoch internally but displays them in the session timezone. DATETIME stores the literal date string without timezone conversion.

MongoDB

// MongoDB uses milliseconds
db.events.insertOne({
  createdAt: new Date(),           // ISODate
  epochMs: Date.now(),             // Number
  epochS: Math.floor(Date.now() / 1000)  // Number
});

// Query with epoch
db.events.find({
  createdAt: { $gte: new Date(1705312200 * 1000) }
});

MongoDB's Date type stores milliseconds since epoch internally but displays as ISO strings. For TTL indexes, MongoDB requires a Date type, not a raw integer.

Redis

# Store epoch as string value
SET event:1:created_at 1705312200

# Use epoch for expiration
EXPIREAT mykey 1705312200   # Expire at specific epoch
EXPIRE mykey 3600            # Expire in 3600 seconds

Recommendations

• Use native datetime types (TIMESTAMPTZ, Date) for most applications
• Use epoch integers when portability across systems is the top priority
• Always use BIGINT (not INT) to avoid the Y2K38 overflow
• Store in UTC and convert to local time at the application layer