Storing Timestamps in Databases: DATETIME vs INT vs BIGINT

Three ways to store a timestamp

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

MySQL: TIMESTAMP vs DATETIME vs INT

MySQL has two date-time types that look similar but behave very differently — and one of them has a hard expiry date. TIMESTAMP is convenient when you want automatic UTC/session-timezone conversion, but its historical 32-bit range makes it risky for future-facing product data. DATETIME stores the literal date and time you provide, which is usually clearer when the application standardizes on UTC before writing.

• 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, no timezone. Range 1000-01-01 to 9999-12-31. Not affected by Y2038.
• DATETIME: does not convert timezones — you control UTC at the application level
• Recommendation: use DATETIME with explicit UTC values for new tables to avoid the 2038 limit

PostgreSQL: TIMESTAMPTZ is the right choice

PostgreSQL's TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ) stores timestamps as UTC microseconds 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 instant 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.

• 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 float from any TIMESTAMP column
• TO_TIMESTAMP(epoch): converts Unix seconds back to a TIMESTAMPTZ

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.

• All three types 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 = '2024-01-01'
• VARCHAR timestamps are the worst for performance — string comparison is not date-aware

When BIGINT epoch storage makes sense

BIGINT is reasonable when the data is event-like, append-heavy, and already produced as Unix time by another system. Analytics pipelines, telemetry streams, queues, and compact binary protocols often use epoch milliseconds because numeric values are fast to compare and language-neutral. The tradeoff is readability: humans need a converter, and SQL date arithmetic becomes more verbose.

• Use BIGINT for Unix milliseconds if JavaScript clients produce the events directly
• Use BIGINT for Unix seconds if the source system is Unix-style and second precision is enough
• Document the unit in the column name: created_at_ms is clearer than created_at_epoch
• Add a generated datetime column if analysts need readable SQL queries
• Avoid INT for modern future-facing timestamps because of 32-bit range limits

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.

• Event logs: created_at TIMESTAMPTZ in PostgreSQL, or created_at DATETIME in UTC for MySQL
• JavaScript event ingestion: created_at_ms BIGINT plus clear API documentation
• Recurring local schedules: local_date, local_time, and timezone_id, then compute the next instant
• Expiration timestamps: expires_at as native datetime or expires_at_seconds with explicit Unix seconds
• Audit tables: keep both created_at and updated_at as native datetime columns for readable debugging

Database timestamp FAQ

Should I store UTC or local time in a database?

Store UTC for event timestamps and convert to local time when displaying. Store a timezone identifier separately when the user's local wall-clock intent matters, such as recurring meetings or business hours.

Is BIGINT better than TIMESTAMP?

Not generally. BIGINT is useful for numeric epoch pipelines, but native datetime types are easier for SQL date arithmetic, readable debugging, and timezone-aware output.

Should MySQL use TIMESTAMP or DATETIME?

For new application tables, DATETIME with UTC values is often safer because it avoids the 2038 range limit and does not silently depend on session timezone conversion.

Should I store timestamps as UTC or with a timezone?

Store the instant in UTC (TIMESTAMPTZ in PostgreSQL, or DATETIME with UTC values in MySQL) and convert to local time on display. Keep a separate IANA timezone column only when you must reconstruct a user's local wall-clock intent, such as recurring meetings.