Timestamps in Databases: Best Practices for Developers
How you store timestamps in a database has long-lasting consequences for your application's correctness, performance, and maintainability. Decisions made early — like which column type to use, whether to store UTC or local time, and whether to use seconds or milliseconds — are difficult to change later once data accumulates. This article covers the most important best practices for handling timestamps in relational and document databases.
Choosing the Right Column Type for Timestamps
The first decision is which data type to use for timestamp columns. Each database offers several options, and the choice matters. In PostgreSQL, you have TIMESTAMP (stores date and time without timezone info) and TIMESTAMPTZ (stores date and time with timezone info). Despite the name, TIMESTAMPTZ does not actually store the timezone — it converts the input to UTC and stores it as UTC. It does, however, correctly handle conversion when reading back the value based on the session timezone. Best practice in PostgreSQL is to always use TIMESTAMPTZ for any timestamp that represents a real-world moment in time. In MySQL, TIMESTAMP columns store values in UTC and automatically convert to/from the server's timezone on read/write — convenient but dangerous if the server timezone ever changes. DATETIME columns store the value as-is with no timezone conversion. Best practice in MySQL is to use DATETIME and always insert UTC values explicitly, which avoids the implicit conversion behavior. Alternatively, store timestamps as BIGINT (integer) columns. This is the most portable approach and eliminates all database-specific timezone handling. Store UTC epoch seconds or milliseconds as a plain integer. This approach is faster for range queries, avoids DST edge cases, and is trivially portable between database engines. The tradeoff is less convenient built-in date functions, though most databases support conversion functions like FROM_UNIXTIME() (MySQL) or TO_TIMESTAMP() (PostgreSQL) for querying. In MongoDB, the native Date type stores UTC milliseconds internally, which aligns with JavaScript's behavior. Use it for any date or time value.
Always Store UTC, Convert at Display
The single most impactful rule for timestamp management is: always store UTC, convert to local time only when displaying to users. Storing in local time creates enormous problems. If your application starts serving users in a different timezone, or if the server is migrated to a different region, all existing timestamps become wrong. DST transitions create gaps (one hour is skipped) and ambiguities (one hour is repeated) in local time that do not exist in UTC. Comparing timestamps from different local timezones requires knowing each timezone, which is complex. UTC has none of these problems. UTC never observes DST. A UTC timestamp unambiguously represents a single instant in time regardless of where the server or user is located. Conversion to local time is done at the application layer, in the frontend, or in the API response — at the very last step before a human sees the value. In practice, this means: all server-side code writes UTC to the database. The API returns UTC timestamps (as epoch integers or ISO 8601 with Z suffix). The frontend converts to the user's local timezone using the browser's timezone (from Intl.DateTimeFormat or a library) for display only. A useful verification: if you see a timestamp in your database and it looks like the current local time of your development machine, you are probably storing local time — a warning sign. UTC timestamps at midnight UTC look like different local times depending on where you run the query.
Indexing and Querying Timestamps Efficiently
Time-range queries are among the most common database operations. Ensuring they are fast requires correct indexing and query patterns. Always index timestamp columns used in WHERE clauses or ORDER BY clauses. A range query like WHERE created_at BETWEEN 1715500800 AND 1715587200 requires an index on created_at to avoid a full table scan. For tables with millions of rows, the difference between an indexed and unindexed timestamp query can be seconds versus milliseconds. Avoid wrapping timestamp columns in functions in WHERE clauses, as this prevents index use. For example: WHERE YEAR(created_at) = 2026 cannot use an index on created_at in most databases. Instead, use a range: WHERE created_at BETWEEN 1735689600 AND 1767225599 (the epoch range for 2026). For queries that filter by date in a specific timezone, do the timezone math at the application layer before querying, then pass UTC timestamps to the query. Do not ask the database to do timezone conversion in the WHERE clause — it prevents index use and is slow. Partitioned tables can dramatically improve performance for time-series data. Partitioning a large table by time range (e.g., monthly partitions) means queries that include a time filter only scan the relevant partition. Both PostgreSQL and MySQL support range partitioning on integer timestamp columns. For analytics queries that aggregate by day, week, or month, pre-computing a DATE column (UTC date without time) alongside the timestamp column avoids repeated conversion at query time. An index on this derived date column makes daily aggregation queries very fast.
Migration and Consistency Across Services
Real applications grow. Multiple services, microservices, or third-party integrations may write timestamps to the same database or consume timestamps from it. Consistency is essential. Document your timestamp convention explicitly. In your database schema documentation and API spec, state: all timestamps are UTC epoch seconds stored as BIGINT, or whatever your choice is. This prevents future developers from accidentally introducing millisecond timestamps or local-time values into columns that expect something different. Use a database migration tool (Flyway, Liquibase, Alembic) when changing timestamp column types. Changing from local time to UTC, or from DATETIME to BIGINT, requires a migration that converts existing data. Run such migrations carefully with backups and verification. For microservice architectures, agree on a timestamp format in your internal event schema and enforce it with schema validation (JSON Schema, Protocol Buffers, or Avro). Inconsistency between services — one emitting seconds, another expecting milliseconds — is a common source of hard-to-diagnose bugs. Audit columns like created_at and updated_at deserve special attention. Set their default value at the database level (DEFAULT NOW() in PostgreSQL or DEFAULT CURRENT_TIMESTAMP in MySQL) to ensure every row has a timestamp even if the application layer forgets to set it. Index them even if you do not expect to query by them — they are invaluable for debugging and data recovery.
Frequently Asked Questions
- Should I use a TIMESTAMP or BIGINT column for Unix timestamps in PostgreSQL?
- Both work well. TIMESTAMPTZ is idiomatic PostgreSQL and gives you access to built-in date functions, operators, and more readable query results. BIGINT is more portable and slightly faster for pure range comparisons. If you need portability across multiple database engines or are doing heavy time-series work, BIGINT epoch milliseconds is a solid choice. For standard CRUD applications, TIMESTAMPTZ with UTC values is the most maintainable option.
- How do I convert a database timestamp to a readable date in SQL?
- In MySQL: SELECT FROM_UNIXTIME(your_column) converts epoch seconds to a DATETIME. In PostgreSQL: SELECT TO_TIMESTAMP(your_column) converts epoch seconds to TIMESTAMPTZ. Both support formatting: MySQL uses DATE_FORMAT, PostgreSQL uses TO_CHAR. For example in PostgreSQL: SELECT TO_CHAR(TO_TIMESTAMP(created_at), 'YYYY-MM-DD HH24:MI:SS') FROM orders. For quick spot-checks without writing SQL, paste the value into our online converter.
- What is the best way to handle the Year 2038 problem in databases?
- Migrate TIMESTAMP columns to BIGINT or to DATETIME/TIMESTAMPTZ, which use 64-bit storage. MySQL's TIMESTAMP type is 32-bit and will overflow in 2038. MySQL's DATETIME type is safe. PostgreSQL's TIMESTAMP types are already 64-bit and are not affected. For new systems, always design with 64-bit storage. For legacy systems using TIMESTAMP in MySQL, plan a migration before 2038 if the system stores future dates.