How to Store Unix Timestamps in a Database (MySQL, Postgres, Mongo)

Three ways to store a timestamp

Store the instant as UTC. Use a native datetime column when you need date arithmetic; use BIGINT epoch when you need raw throughput; never use VARCHAR. Most databases offer at least three options — a native datetime type (TIMESTAMP, DATETIME, TIMESTAMPTZ), a plain integer (INT or BIGINT), or a string (VARCHAR). Each has different trade-offs for storage size, query ergonomics, timezone handling, and future-proofing. For most product databases, a native datetime column is the best default because the database can compare, index, truncate, group, and format the value as time instead of as an anonymous number.

• Native datetime type — best for date arithmetic, timezone conversion, and readability
• BIGINT integer — good for high-throughput inserts and simple numeric range queries
• VARCHAR string — almost always wrong: string comparison of dates only works with strict ISO 8601 format
• INT integer — avoid for future timestamps unless you have fully checked the Year 2038 boundary

At-a-glance comparison: bytes, range, and when to use each

Picking a column type is mostly a trade-off between storage size, the date range you need to represent, and whether you want the database or the application to handle timezones. The data points below answer the head-to-head questions that drive most decisions. The eight-byte cost of BIGINT and TIMESTAMPTZ is the same as a 64-bit floating-point column — never a budget concern in a modern OLTP database.

• MySQL TIMESTAMP — 4 bytes, range 1970-01-01 to 2038-01-19, session-timezone aware. Use for legacy code only.
• MySQL DATETIME — 5 bytes, range 1000 to 9999, no timezone (app handles UTC). Default for new MySQL tables.
• PostgreSQL TIMESTAMPTZ — 8 bytes, range 4713 BC to AD 294276, stores UTC + converts on output. Default for new PostgreSQL tables.
• PostgreSQL TIMESTAMP (without tz) — 8 bytes, same range, no conversion. Use only for timezone-naive wall-clock values.
• BIGINT Unix seconds — 8 bytes, range ±292 billion years, no timezone (numeric). Default for high-throughput pipelines.
• BIGINT Unix milliseconds — 8 bytes, range ±292 million years, no timezone. Default when JavaScript clients write the events.
• MongoDB BSON Date — 8 bytes, range ±292 million years, no timezone (UTC instant). Default for MongoDB.
• SQLite INTEGER (Unix seconds) — variable bytes, no native range limit, no timezone. Most common SQLite choice.
• DynamoDB Number (Unix seconds) — variable bytes, no timezone. Required for the TTL feature.
• ISO 8601 string — 20 to 25 bytes, full range as text, timezone embedded. Use only for logs intended for humans.
• VARCHAR free-form — variable bytes, broken sorting, broken comparison. Never.

MySQL: TIMESTAMP vs DATETIME vs BIGINT

MySQL has two date-time types that look similar but behave very differently — and one of them has a hard expiry date in 2038. TIMESTAMP is convenient when you want automatic UTC/session-timezone conversion, but its historical 32-bit signed range makes it risky for any data with a date past 2038-01-19. DATETIME stores the literal date and time you provide and does no conversion, which is usually clearer when the application standardizes on UTC before writing. BIGINT epoch storage is a third option when you want raw numeric throughput or you are interoperating with Unix-style code outside the database.

• TIMESTAMP — stored as 32-bit Unix seconds internally; limited to 1970-01-01 through 2038-01-19
• TIMESTAMP — auto-converts between UTC and the session timezone on insert/read
• DATETIME — stores the literal date-time with no timezone; range 1000-01-01 to 9999-12-31; not affected by Y2038
• DATETIME — does not convert timezones, so you must standardize on UTC at the application level
• BIGINT — 8 bytes, immune to Y2038, no timezone conversion, costs you the readability of SELECT-ing the column directly
• Recommendation: use DATETIME with explicit UTC values for new MySQL tables, or BIGINT Unix ms when JavaScript clients write the records

• MySQL — The DATE, DATETIME, and TIMESTAMP Types (official docs)

The Year 2038 limit and how it affects MySQL TIMESTAMP

TIMESTAMP is stored as a signed 32-bit Unix second count, so it wraps at 2147483647 — that is, 2038-01-19 03:14:07 UTC. Inserting a value past that point fails or silently truncates depending on MySQL's sql_mode. DATETIME, BIGINT, and PostgreSQL TIMESTAMPTZ are all unaffected. If you have an existing TIMESTAMP column you cannot easily migrate, plan the column-type change before 2038 or before you start writing far-future values (subscription expirations, scheduled events). The site's dedicated Y2038 guide covers the migration path.

• The Year 2038 Problem (Y2K38) — full guide

PostgreSQL: TIMESTAMPTZ is the right choice

PostgreSQL's TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ) stores the UTC instant in 8 bytes internally and converts to the session timezone on output. It is the safest and most correct option for most use cases because it represents a real moment in time. The name can be misleading — TIMESTAMPTZ does not store the original timezone label such as America/New_York. It stores the instant, then displays it according to the current session timezone. If you also need to reconstruct the user's local wall-clock intent, store the IANA timezone name in a separate column.

• TIMESTAMPTZ — stores UTC, converts to session timezone on output, portable and DST-safe
• TIMESTAMP (no time zone) — stores the literal value with no conversion; use only for timezone-naive data
• EXTRACT(EPOCH FROM col) — returns Unix seconds as a double-precision float from any timestamp column
• TO_TIMESTAMP(epoch) — converts Unix seconds back to a TIMESTAMPTZ
• AT TIME ZONE 'America/New_York' — converts a TIMESTAMPTZ to a wall-clock value in the named zone
• Microsecond precision out of the box — no need to opt in to fractional seconds as you do in MySQL

• PostgreSQL — Date/Time Types (official docs)
• PostgreSQL Fixes timeofday() Timezone Memory-Disclosure Flaw (site news)

SQLite: TEXT, INTEGER, or REAL

SQLite is unusual: it has no dedicated date or time type. Dates are stored as one of three storage classes — TEXT (ISO 8601 strings), INTEGER (Unix seconds since the epoch), or REAL (Julian Day numbers). The built-in date functions (date(), time(), datetime(), strftime()) accept all three forms transparently, so you can mix and match if you really need to. In practice, almost every SQLite-backed application picks one form and sticks with it. INTEGER Unix seconds is the most common choice — compact, easy to index, and trivial to compare numerically. TEXT ISO 8601 wins when humans inspect the database directly.

• TEXT — ISO 8601 strings (e.g. '2026-06-20T09:25:15.000Z'); readable; sortable lexically when the format is consistent and ends in Z
• INTEGER — Unix seconds since 1970-01-01 UTC; compact; sortable numerically; the most common choice
• REAL — Julian Day number; mostly for legacy or astronomy; uncommon in app code
• All three integrate with strftime(): SELECT strftime('%Y-%m-%d', col) — works regardless of storage class
• Document the format in the column comment or table schema; SQLite will not enforce it for you

• SQLite — Date and Time Datatype (official docs)

MongoDB: BSON Date and integer alternatives

MongoDB stores dates natively as the BSON Date type — a signed 64-bit integer of milliseconds since the Unix epoch. The driver maps it to a Date object in JavaScript, datetime in Python, and ISODate in the mongo shell. Native BSON Date is the right default for most documents; it indexes well, is timezone-neutral (always UTC under the hood), and is the only type that TTL indexes work with. Plain integer fields are still useful when you want millisecond precision but do not need the Date semantics, or when you are storing a separate seconds-precision field for compatibility with external systems.

• BSON Date — 8 bytes, signed 64-bit, milliseconds since 1970 UTC
• Indexes natively — range queries on Date are fast and idiomatic
• Use ISODate('2026-06-20T00:00:00Z') in the shell to create comparable values
• For multi-precision needs, use Decimal128 or a sub-document with seconds + nanos; Date itself is millisecond precision
• TTL indexes only work on BSON Date fields — integer epoch fields cannot use expireAfterSeconds
• Aggregation pipeline date operators ($dateFromString, $dateToString) make round-tripping ISO 8601 easy

• MongoDB — BSON Date type (official docs)

DynamoDB: epoch attributes and TTL

DynamoDB has no dedicated date type. Two patterns dominate: ISO 8601 strings for human readability and lexicographic sorting, or epoch numbers for compactness and TTL support. The Time-To-Live feature is the deciding factor for many designs — it requires the attribute to be a Number representing Unix seconds (not milliseconds, not strings). Mixing seconds and milliseconds across tables is the most common source of bugs in DynamoDB schemas; the database will not detect or protect you from the unit drift.

• TTL requires the attribute to be a Number representing Unix seconds — not milliseconds, not strings
• Strings store ISO 8601 with full timezone info but cost more bytes and sort lexicographically (include the Z suffix consistently)
• Range queries on a sort-key epoch number are extremely cheap and align with DynamoDB's query model
• Avoid mixing seconds and milliseconds across tables — DynamoDB will not protect you from the unit drift
• DynamoDB Streams emit an ApproximateCreationDateTime in ISO 8601 regardless of how you stored the original value

• AWS — DynamoDB Time-To-Live (official docs)

Redis: sorted sets with epoch scores

Redis is a key-value store, not a relational database, but it shows up in almost every product stack as a cache, queue, rate limiter, or leaderboard. The dominant pattern for time-based data is a Sorted Set (ZSET) where the score is a Unix timestamp. This gives you O(log N) range queries by time, an easy way to expire old entries with ZRANGEBYSCORE + ZREMRANGEBYSCORE, and a single command for the most-recent-N use case. Use this pattern for rate limiters (count events in the last 60 seconds), leaderboards (sort by submission time), and event queues with time-based replay.

• ZADD key <epoch_seconds> <member> — insert a timed entry
• ZRANGEBYSCORE key <start_epoch> <end_epoch> — range query by time, O(log N)
• ZREMRANGEBYSCORE key -inf <cutoff_epoch> — evict everything older than a cutoff
• Score is a 64-bit double — accurate to the nearest millisecond as a Unix-time value, safe up to ±9 quadrillion seconds
• Combine with EXPIRE on the parent key for two-level TTL — fine-grained range query + cheap whole-set cleanup
• For pure expiration without range query, prefer the standard EXPIRE command on individual keys (Redis 7.4+ supports expiration on hash fields)

• Redis — Sorted Sets (official docs)

Time-series databases: InfluxDB, TimescaleDB, ClickHouse, Prometheus

When the dominant query pattern is range-scans by time at scale — observability, IoT, financial ticks, telemetry — a purpose-built time-series database outperforms a row-store by orders of magnitude. The common ground across InfluxDB, TimescaleDB, ClickHouse, and Prometheus is that timestamps are first-class: stored as int64 nanoseconds, used as the partition key, and exposed through specialized window-function operators. The trade-off is that arbitrary OLTP queries (joins, sub-selects, transactions) are weaker. Most teams pair a row-store with a time-series database rather than choosing one.

• InfluxDB — timestamps stored as int64 nanoseconds; tags and fields keyed by time; SQL-like query layer via Flux/InfluxQL
• TimescaleDB — PostgreSQL extension; uses TIMESTAMPTZ on hypertables; transparent time-based partitioning
• ClickHouse — DateTime64 type stores nanoseconds; MergeTree tables partition by time efficiently
• Prometheus — millisecond Unix timestamps; PromQL provides time-window operators (rate, irate, increase)
• Common pattern: write hot data to a time-series DB; replicate aggregated rollups into your row-store

• InfluxDB — Time precision and timestamps (official docs)

Indexing and query performance

For normal application tables, the performance difference between native datetime columns and BIGINT epoch columns is rarely the deciding factor. Query shape, index design, partitioning, and row count matter more. Choose the type that keeps the meaning correct first, then index it for the range queries your application actually runs. Use half-open boundaries — WHERE event_time >= start AND event_time < end — to avoid the precision pitfalls of inclusive-endpoint queries.

• All three types (native datetime, BIGINT, REAL) support B-tree indexes and efficient range queries
• BIGINT integers are marginally faster for equality and range scans on very high-volume tables
• Native datetime types allow indexed date-part queries: WHERE created_at::date = '2026-06-20'
• VARCHAR timestamps are the worst for performance — string comparison is not date-aware unless the format is strict ISO 8601 with consistent zero-padding
• Partition large tables by month or by year using the timestamp column; both MySQL and PostgreSQL support this natively
• Always test the actual EXPLAIN plan — a poorly-chosen index can make any column type slow

• Half-open vs inclusive boundaries in SQL — see /blog/end-of-2026-unix-timestamp

Recommended schema patterns

For most web applications, store an instant in UTC and store the user's preferred timezone separately only when you need to reconstruct local wall-clock intent. A meeting scheduled for 9:00 AM America/New_York is different from an event log created at a precise UTC instant; model those cases differently. When JavaScript clients write the events directly, prefer BIGINT Unix milliseconds — it matches what Date.now() returns and keeps the data round-trip-safe across the network without timezone reinterpretation.

• Event logs (general): created_at TIMESTAMPTZ in PostgreSQL, or created_at DATETIME in UTC for MySQL
• JavaScript event ingestion: created_at_ms BIGINT — matches Date.now() exactly; document the unit in the column name
• Recurring local schedules: local_date, local_time, and timezone_id (IANA name); compute the next instant on read
• Expiration timestamps: expires_at as native datetime when the database has good built-in expiry features (Postgres), or expires_at_seconds BIGINT when working with DynamoDB TTL
• Audit tables: created_at and updated_at as native datetime columns for readable debugging
• Append-only telemetry: BIGINT or a time-series database, partitioned by day or hour

• Timezone-Correct Date Formatting in JavaScript (companion guide)
• JavaScript Temporal API ships in Node.js 26 (site news)

Common mistakes (don't do these)

Most database timestamp bugs come from a small set of anti-patterns. Reading them once is cheaper than debugging them in production. The recurring theme is the same as in JavaScript code: the developer assumes a fixed offset, a fixed unit, or a fixed type — and gets bitten when DST shifts, when a client writes the value in a different precision, or when the source system changes. Pick a single representation, document it, and validate at write time.

• Storing VARCHAR free-form dates ('Jun 20, 2026 9:25am') — breaks sorting, breaks comparison, breaks every date function in the database
• Mixing Unix seconds and Unix milliseconds in the same column — the database cannot detect the mismatch, only the reader can, usually after data corruption
• Using MySQL TIMESTAMP for far-future dates (subscription expirations past 2038) — silent truncation or insert failure on the Year 2038 boundary
• Relying on the session timezone (MySQL TIMESTAMP, AT TIME ZONE in Postgres without an explicit cast) — the same query returns different rows depending on which connection runs it
• Storing both a UTC datetime and a string copy of the local time — they diverge at every DST transition unless something keeps them in sync
• Storing INT (32-bit) instead of BIGINT (64-bit) for epoch values — same Year 2038 problem as MySQL TIMESTAMP, with none of the convenience
• Using strftime() to convert in the application layer when the database can do it natively — slower and harder to maintain
• Computing date arithmetic in the application instead of the database — DST and leap-year edge cases are easy to miss when you write the math yourself

Migration recipes: changing column types safely

Migrating timestamp columns in production is risky because the data semantics change as well as the storage. Always run the migration on a copy first, verify the row count and sample values match, and keep the original column until you have confirmed the new one is correct end-to-end. For large tables, prefer an online migration that adds a new column, dual-writes for a period, backfills in chunks, and then renames — rather than ALTER TABLE on the original column.

• VARCHAR to DATETIME (MySQL): ALTER TABLE t ADD COLUMN created_at_new DATETIME; UPDATE t SET created_at_new = STR_TO_DATE(created_at, '%Y-%m-%d %H:%i:%s'); verify; swap; drop old column
• VARCHAR to TIMESTAMPTZ (Postgres): ALTER TABLE t ADD COLUMN created_at_new TIMESTAMPTZ; UPDATE t SET created_at_new = (created_at || ' UTC')::TIMESTAMPTZ; verify; swap; drop
• INT to BIGINT (MySQL / Postgres): ALTER TABLE t ALTER COLUMN epoch_seconds TYPE BIGINT; safe because INT widens to BIGINT without conversion — no down-cast risk
• TIMESTAMP to DATETIME (MySQL): ALTER TABLE t MODIFY COLUMN created_at DATETIME; the literal date-time copies over; pre-2038 values are unchanged; DST drift on session-timezone-aware writes is exposed
• Naive to timezone-aware: rename the old column, add the new TIMESTAMPTZ column, backfill by interpreting old values as UTC (SET new_col = old_col AT TIME ZONE 'UTC' in Postgres), verify, swap, drop
• Always: dump a small sample (10k rows) before and after; diff the parsed-instant values to catch off-by-one offsets and DST shifts

FAQ

Common questions about timestamp storage in SQL and NoSQL databases. The full list is also embedded as FAQPage JSON-LD for AI search readability.