Overview
PostgreSQL supports epoch conversion with to_timestamp and extract(epoch from ...). For timestamped events, timestamptz is often the best storage type because it represents an instant and formats according to the session timezone.
This guide focuses on practical implementation choices: how values should be stored, how they should cross API boundaries, and where developers most often introduce timezone or precision bugs. The safest pattern is to keep the stored value unambiguous, document the unit, and format the date only when it reaches a human-facing interface.
When to use it
Use PostgreSQL epoch conversion for analytics queries, imports, API normalization, and debugging event streams.
For production systems, also consider how the value will be indexed, logged, serialized, and read by other teams. A timestamp field that is obvious in one programming language can become ambiguous when it is consumed by JavaScript, SQL, mobile clients, or third-party integrations.
Developer examples
Use examples like this as a starting point, then adapt the timezone and precision to your application contract.
SELECT to_timestamp(1717243200);
SELECT extract(epoch FROM now())::bigint;Common pitfalls
timestamp without time zone is not an instant by itself. Use timestamptz for event moments and store local timezone IDs for local scheduling rules.
Most timestamp bugs come from hidden assumptions: local time treated as UTC, seconds treated as milliseconds, formatted strings parsed without offsets, or narrow integer columns copied from old examples. Add tests for boundary dates and document the expected unit beside every external timestamp field.
FAQ
What does to_timestamp return?
It returns a timestamp with time zone value representing the epoch instant.
Should I store epoch integers or timestamptz?
For most application events, timestamptz is clearer. Epoch integers are useful for compatibility and compact interchange.