SQL (PostgreSQL) Unix Timestamp Guide
PostgreSQL uses EXTRACT(EPOCH FROM ...) to get Unix timestamps and TO_TIMESTAMP() to convert them back. PostgreSQL's robust date/time support includes timezone-aware types and arbitrary precision. The EXTRACT function works with any timestamp or interval value.
Unix timestamps are a universal way to represent a specific moment in time as a single integer, counting the seconds elapsed since January 1, 1970 (the Unix epoch). Working with timestamps in SQL (PostgreSQL) is a common task for developers building applications that log events, schedule jobs, compare dates, or communicate with APIs. The examples below cover the three most essential operations: retrieving the current timestamp, converting a timestamp into a human-readable date, and converting a date back into a timestamp.
Get Current Timestamp
The most common starting point is to capture the current moment as a Unix timestamp. In SQL (PostgreSQL), you can obtain the number of seconds (or milliseconds) since the epoch using built-in functions. This value is useful for recording when an event occurred, setting cache expiry times, or generating time-based identifiers.
Get Current Unix Timestamp
-- Get current Unix timestamp
SELECT EXTRACT(EPOCH FROM NOW())::INTEGER;
-- Result: 1706745600
-- With milliseconds
SELECT EXTRACT(EPOCH FROM NOW()) * 1000;Convert Timestamp to Date
Once you have a Unix timestamp, you often need to display it in a human-readable format. Converting a raw integer like 1706745600 into a formatted date string such as "2024-02-01 00:00:00" makes it meaningful to end users. The following SQL (PostgreSQL) code demonstrates how to perform this conversion with proper timezone handling.
Convert to Date
-- Convert Unix timestamp to timestamp
SELECT TO_TIMESTAMP(1706745600);
-- Result: 2024-02-01 00:00:00+00
-- Format as string
SELECT TO_CHAR(
TO_TIMESTAMP(1706745600),
'YYYY-MM-DD HH24:MI:SS'
);Convert Date to Timestamp
The reverse operation is equally important. When users provide a date through a form or when your application reads dates from a file, you need to convert them into Unix timestamps for storage, comparison, or transmission via APIs. Here is how to convert a date or date string into a Unix timestamp in SQL (PostgreSQL).
Convert to Timestamp
-- Convert timestamp to Unix epoch
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2024-02-01 00:00:00');
-- Result: 1706745600
-- From column
SELECT EXTRACT(EPOCH FROM created_at) FROM users;Common Pitfalls in SQL (PostgreSQL)
Working with timestamps can be error-prone, especially across different platforms and timezone configurations. Being aware of these common mistakes will help you write more robust SQL (PostgreSQL) code and avoid subtle bugs that are difficult to trace in production.
- 1EXTRACT(EPOCH ...) returns a float with microsecond precision — cast to INTEGER for Unix seconds
- 2TO_TIMESTAMP() returns timestamptz (with timezone) — compare with AT TIME ZONE for local times
- 3TIMESTAMP and TIMESTAMPTZ are different types — TIMESTAMPTZ stores UTC internally
- 4PostgreSQL's epoch starts at 2000-01-01 internally but EXTRACT(EPOCH) returns Unix epoch
Best Practices for Timestamp Handling
Regardless of the programming language, following a few best practices will keep your timestamp code reliable. Always store and transmit timestamps in UTC to avoid timezone ambiguity. Use seconds-based Unix timestamps unless your application requires sub-second precision, in which case milliseconds or microseconds are appropriate. When displaying dates to users, convert from UTC to their local timezone only at the presentation layer. Document whether your APIs expect seconds or milliseconds, as mixing the two is one of the most frequent sources of timestamp bugs.
Frequently Asked Questions
Related Tools
Timestamp Guides for Other Languages
Need Unix timestamp examples for a different programming language? Browse our complete collection of language-specific guides with copy-paste code snippets.