How to Store Unix Timestamps in a Database

Quick answer

If you are designing a normal product table, start here:

Database	Default choice	Why
PostgreSQL	timestamptz	stores one instant; strong date functions
MySQL	DATETIME(3) or DATETIME(6) in UTC	avoids MySQL TIMESTAMP 2038/session-timezone traps
SQLite	INTEGER epoch or TEXT ISO 8601	no native date type; pick one format and document it
MongoDB	BSON Date	native signed 64-bit millisecond instant
DynamoDB	Number epoch seconds for TTL; ISO string or number for sort keys	TTL requires Unix seconds
Redis	sorted-set score as epoch seconds or milliseconds	fast time-window range queries
TimescaleDB	timestamptz time column	PostgreSQL semantics plus time partitioning
ClickHouse	DateTime64(3/6/9)	analytical timestamp with chosen precision

The simplest rule:

Store exact events as UTC instants.
Store local schedules as local date/time plus IANA timezone.
Put the epoch unit in the field name when you use integers.

First decide what the timestamp means

Most database timestamp bugs start before the column type is chosen. The team has not decided whether the value is an exact instant or a local wall-clock rule.

Product meaning	Store
"This row was created at this exact instant"	UTC instant: native timestamp or epoch integer
"This payment expires at this exact instant"	UTC instant plus documented unit
"Show this audit event in the viewer's timezone"	UTC instant; convert at display time
"Meeting every Monday at 9 AM in New York"	weekday, local_time, time_zone
"Birthday or holiday with no time of day"	date-only value, not midnight UTC
"DynamoDB should delete this item"	Number epoch seconds in the TTL attribute
"Redis should query events in the last 60 seconds"	sorted-set score as epoch seconds or milliseconds

Do not store a formatted local string as the only truth:

2026-06-20 09:00

That value is incomplete. It might mean UTC, server local time, New York time, Tokyo time, or something else.

Store the instant:

{
  "created_at": "2026-06-20T14:30:00Z",
  "created_at_ms": 1781965800000
}

For a recurring local rule, store the wall-clock intent:

{
  "weekday": "Monday",
  "local_time": "09:00",
  "time_zone": "America/New_York"
}

Native timestamp, BIGINT, or string?

There are three broad storage patterns.

Pattern	Good for	Watch out for
Native timestamp type	SQL date math, indexing, readable queries, date truncation	database-specific timezone behavior
BIGINT epoch	event pipelines, JavaScript milliseconds, compact numeric range queries	unit mistakes: seconds vs milliseconds vs microseconds
ISO 8601 string	logs, exports, human inspection, systems without date types	weak date arithmetic; inconsistent formats break sorting

Native timestamp types are usually best for product databases because the database understands the value as time. You can index it, compare it, group by day, truncate to month, and use time functions directly.

BIGINT is best when the surrounding system already speaks epoch integers. Examples:

• created_at_ms from Date.now()
• expires_at_seconds for DynamoDB TTL
• event_time_ns from telemetry
• score in a Redis sorted set

Strings are acceptable only if they are strict ISO 8601 or RFC 3339, consistently zero-padded, and clearly labeled as UTC or offset-bearing:

2026-06-20T14:30:00Z
2026-06-20T10:30:00-04:00

Free-form strings are the trap:

Jun 20, 2026 9:25am
06/20/26 09:25
Saturday morning

Those strings are poor keys, poor filters, and poor migration inputs.

MySQL: DATETIME vs TIMESTAMP vs BIGINT

For new MySQL application tables, the conservative default is DATETIME(3) or DATETIME(6) containing UTC values.

Example:

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  created_at DATETIME(3) NOT NULL,
  paid_at DATETIME(3) NULL,
  created_at_ms BIGINT NULL,
  CHECK (created_at >= '2000-01-01 00:00:00')
);

Why not always TIMESTAMP?

MySQL documents three important differences:

Type	Range	Timezone behavior	Storage
TIMESTAMP	1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC	converts from session timezone to UTC on write and back on read	4 bytes plus fractional seconds
DATETIME	1000-01-01 00:00:00 to 9999-12-31 23:59:59	stores the literal value you provide	5 bytes plus fractional seconds
BIGINT	signed 64-bit integer	no timezone semantics	8 bytes

TIMESTAMP can be fine for legacy rows and short-lived operational data, but it is a risky default for subscriptions, scheduled events, legal records, or anything that might cross 2038.

If you use TIMESTAMP, pin the connection timezone:

SET time_zone = '+00:00';

If you use DATETIME, make the UTC convention visible in code review:

created_at_utc DATETIME(3) NOT NULL

If JavaScript clients write events directly, a parallel integer can help:

created_at_ms BIGINT NOT NULL

Then validate it:

CHECK (created_at_ms BETWEEN 946684800000 AND 4102444800000)

That range covers 2000-01-01 through 2100-01-01 in epoch milliseconds and catches many seconds-vs-milliseconds mistakes.

PostgreSQL: use timestamptz for instants

PostgreSQL's timestamp with time zone, usually written timestamptz, is the right default for exact event times.

Example:

CREATE TABLE events (
  id bigserial PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT now(),
  event_time timestamptz NOT NULL,
  user_time_zone text NULL
);

The name is confusing. timestamptz does not store the original timezone label. PostgreSQL stores the instant and displays it in the current session timezone. If you insert 2026-06-20 09:00:00 America/New_York, PostgreSQL can resolve the instant, but it will not remember that the user typed America/New_York.

So use two columns when local context matters:

CREATE TABLE meetings (
  id bigserial PRIMARY KEY,
  starts_at timestamptz NOT NULL,
  local_date date NOT NULL,
  local_time time NOT NULL,
  time_zone text NOT NULL
);

Common Postgres operations:

-- Unix seconds from a timestamptz
SELECT extract(epoch FROM created_at) AS created_at_seconds
FROM events;

-- Unix seconds back to a timestamptz
SELECT to_timestamp(1700000000);

-- Display an instant in New York wall-clock time
SELECT created_at AT TIME ZONE 'America/New_York'
FROM events;

Prefer half-open ranges:

WHERE created_at >= $1
  AND created_at <  $2

That avoids missing microsecond rows at the end of the day.

SQLite: choose TEXT or INTEGER deliberately

SQLite has no dedicated date/time type. Official SQLite docs describe three storage forms:

• TEXT: ISO 8601 date/time string
• INTEGER: Unix timestamp
• REAL: Julian day number

For a small app, local database, or mobile client, either of these is reasonable:

CREATE TABLE events_text (
  id INTEGER PRIMARY KEY,
  created_at TEXT NOT NULL
);

CREATE TABLE events_epoch (
  id INTEGER PRIMARY KEY,
  created_at_seconds INTEGER NOT NULL
);

Query examples:

SELECT datetime(created_at_seconds, 'unixepoch')
FROM events_epoch;

SELECT unixepoch(created_at)
FROM events_text;

Use TEXT if humans inspect the .sqlite file and you always write UTC strings such as 2026-06-20T14:30:00Z.

Use INTEGER if range queries and compact storage matter:

CREATE INDEX events_created_at_seconds_idx
ON events_epoch(created_at_seconds);

Avoid mixing seconds and milliseconds in the same column. If you want milliseconds, name it that way:

created_at_ms INTEGER NOT NULL

MongoDB: use BSON Date for application timestamps

MongoDB's native Date is a signed 64-bit integer representing milliseconds since the Unix epoch. That makes it a natural fit for application timestamps.

Example document:

db.orders.insertOne({
  createdAt: new Date("2026-06-20T14:30:00Z"),
  status: "paid"
});

Range query:

db.orders.find({
  createdAt: {
    $gte: ISODate("2026-06-01T00:00:00Z"),
    $lt: ISODate("2026-07-01T00:00:00Z")
  }
});

TTL index:

db.sessions.createIndex(
  { expiresAt: 1 },
  { expireAfterSeconds: 0 }
);

Use BSON Date for createdAt, updatedAt, expiresAt, and event times. Use integer fields only when you need a separate compatibility value:

{
  createdAt: ISODate("2026-06-20T14:30:00Z"),
  createdAtMs: NumberLong("1781965800000")
}

Do not confuse BSON Date with MongoDB's internal BSON Timestamp type. MongoDB documents the timestamp type as internal; application code should generally use BSON Date.

DynamoDB: TTL means epoch seconds

DynamoDB has no dedicated datetime type. You choose String or Number.

The TTL rule is strict: the TTL attribute must be a Number containing Unix epoch time in seconds. String attributes are ignored by the TTL process. A 13-digit millisecond value is still a number, but DynamoDB interprets it as seconds, so the item will not expire when you expect.

Example item:

{
  "pk": "session#123",
  "created_at": "2026-06-20T14:30:00Z",
  "created_at_ms": 1781965800000,
  "expires_at_seconds": 1781969400
}

Common DynamoDB patterns:

Need	Attribute
TTL deletion	expires_at_seconds Number
human-readable export	ISO 8601 string ending in Z
sort key by time	fixed-width ISO string or epoch number
JavaScript client compatibility	epoch milliseconds Number

If you use ISO strings as sort keys, keep the format fixed:

2026-06-20T14:30:00Z
2026-06-20T14:31:00Z
2026-06-20T14:32:00Z

That sorts correctly. Mixed formats do not.

Redis: sorted sets for time windows

Redis is usually not your source-of-truth database, but it is excellent for time-window queries.

Sliding-window rate limiter:

ZADD user:123:requests 1781965800000 request-id-1
ZREMRANGEBYSCORE user:123:requests -inf 1781965740000
ZCOUNT user:123:requests 1781965740000 1781965800000
EXPIRE user:123:requests 120

Use epoch milliseconds as the sorted-set score if you need millisecond resolution. Redis scores are double-precision floats. Current epoch milliseconds are comfortably exact as integers, current epoch microseconds still fit under JavaScript's 2^53 - 1 safe-integer limit, but epoch nanoseconds do not. If you need nanosecond precision, split seconds and nanoseconds instead of putting the whole nanosecond value in the score.

Use a member value that is unique:

request-id-1
1781965800000:uuid

If you only need expiration, use normal key expiration:

SET session:123 payload EX 3600

Use sorted sets when you need to ask "what happened between start and end?"

Time-series databases

Use a time-series database when time is the main access pattern: observability, IoT, financial ticks, metrics, traces, or high-volume event streams.

Different systems use different timestamp models:

System	Typical timestamp model
InfluxDB	line protocol timestamp is nanosecond-precision Unix time unless another precision is specified
TimescaleDB	PostgreSQL hypertables commonly use a timestamptz time column
ClickHouse	DateTime for seconds; DateTime64(3/6/9) for millisecond, microsecond, or nanosecond precision
Prometheus	samples use Unix timestamps with millisecond precision in common exposition/write paths

Do not flatten this to "time-series databases store int64 nanoseconds." Some do. Some do not. The precision is part of the ingestion contract and should be written next to the field name or table definition.

Example ClickHouse schema:

CREATE TABLE events (
  event_time DateTime64(3, 'UTC'),
  service LowCardinality(String),
  value Float64
)
ENGINE = MergeTree
ORDER BY (service, event_time);

Example TimescaleDB shape:

CREATE TABLE conditions (
  time timestamptz NOT NULL,
  device text NOT NULL,
  temperature double precision
);

Indexing timestamp columns

Most timestamp queries are range queries. Design for that first.

Good:

WHERE created_at >= '2026-06-01T00:00:00Z'
  AND created_at <  '2026-07-01T00:00:00Z'

Risky:

WHERE created_at BETWEEN '2026-06-01' AND '2026-06-30'

BETWEEN is inclusive, and date-only strings can hide midnight assumptions. Half-open ranges are easier to reason about at any precision.

Practical indexes:

CREATE INDEX orders_created_at_idx
ON orders (created_at);

CREATE INDEX events_account_time_idx
ON events (account_id, created_at);

For append-heavy tables, partition by time only when the table is large enough for partition pruning to matter. A bad partitioning scheme is worse than a simple B-tree index.

Migration checklist

Timestamp migrations fail when the team changes storage type without proving the instant stayed the same.

Use this process:

1. Add the new column.
2. Backfill in batches.
3. Compare old and new values as UTC instants.
4. Dual-write for a release.
5. Move reads to the new column.
6. Keep the old column until dashboards, exports, and support tools agree.
7. Drop the old column in a later migration.

Examples:

-- MySQL: epoch milliseconds to UTC DATETIME(3)
UPDATE events
SET created_at_utc = FROM_UNIXTIME(created_at_ms / 1000.0)
WHERE created_at_utc IS NULL;

-- PostgreSQL: epoch milliseconds to timestamptz
UPDATE events
SET created_at = to_timestamp(created_at_ms / 1000.0)
WHERE created_at IS NULL;

-- SQLite: epoch seconds to ISO-like UTC text
UPDATE events
SET created_at_text = datetime(created_at_seconds, 'unixepoch')
WHERE created_at_text IS NULL;

After backfill, sample rows around edge cases:

• epoch 0
• negative timestamps if your domain has them
• 2038-01-19 03:14:07 UTC
• daylight-saving transitions for your main user regions
• milliseconds values that would look like year 55000 if treated as seconds

Common database timestamp mistakes

Mistake	Symptom	Fix
created_at is an INT Unix seconds column	future dates fail near 2038	use BIGINT or native timestamp
MySQL TIMESTAMP used for subscription expiry	values past 2038 fail	use DATETIME(3) UTC or BIGINT
Postgres timestamptz expected to remember America/New_York	original timezone is gone	store time_zone separately
DynamoDB TTL stored in milliseconds	items do not expire when expected	store epoch seconds Number
Redis sorted-set score uses epoch nanoseconds	precision loss	use milliseconds or split seconds/nanos
SQLite column mixes seconds and milliseconds	dates land in 1970 or year 55000	name columns with units and validate
ISO strings are not zero-padded	lexicographic order breaks	use strict RFC 3339/ISO 8601
Local display string stored as truth	DST and timezone drift	store UTC instant plus timezone context

The column name is part of the schema contract. Prefer:

created_at
created_at_ms
expires_at_seconds
event_time_ns
time_zone

over:

timestamp
date
time

Recommended patterns by use case

Use case	Recommended schema
PostgreSQL app events	created_at timestamptz NOT NULL DEFAULT now()
MySQL app events	created_at_utc DATETIME(3) NOT NULL
JavaScript ingestion	created_at_ms BIGINT NOT NULL plus unit validation
MongoDB documents	createdAt: Date
SQLite local app	created_at_seconds INTEGER or created_at TEXT
DynamoDB sessions	expires_at_seconds Number for TTL
Redis sliding window	ZSET score as epoch milliseconds
recurring local schedule	local date/time fields plus IANA time_zone
birthday or due date with no time	DATE, not timestamp-at-midnight
observability metrics	time-series database with declared precision

Official references

• MySQL DATETIME and TIMESTAMP
• MySQL data type storage requirements
• PostgreSQL date/time types
• PostgreSQL date/time functions
• SQLite date and time functions
• MongoDB BSON Date
• MongoDB TTL indexes
• DynamoDB TTL
• Redis sorted sets
• InfluxDB line protocol
• Timescale hypertables
• ClickHouse DateTime64

Related guides

• Unix Time to Date
• Date to Epoch
• What Is UTC Time
• Milliseconds vs Seconds
• JavaScript Date.now()
• JavaScript Timezone Guide
• Timestamp Bugs
• Year 2038 Problem