PostgreSQL provides excellent support for Unix timestamps through the EXTRACT(EPOCH FROM ...) function and the to_timestamp() function. PostgreSQL handles time zones elegantly and provides precise timestamp operations. This guide covers all essential timestamp operations in PostgreSQL.
How to get the current epoch time
SELECT extract(epoch FROM now());Convert from human-readable date to epoch
SELECT extract(epoch FROM date('2000-01-01 12:34'));With timestamp: SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2018-02-16 20:38:40-08'); With interval: SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
Convert from epoch to human-readable date
SELECT to_timestamp(epoch);PostgreSQL version 8.1 and higher: Source Older versions: SELECT TIMESTAMP WITH TIME ZONE 'epoch' + epoch * INTERVAL '1 second';
Additional PostgreSQL Examples
Working with Time Zones
-- Convert timestamp with time zone to epoch
SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2024-01-01 12:00:00+00');
-- Convert epoch to timestamp with time zone
SELECT to_timestamp(1609459200) AT TIME ZONE 'UTC';
-- Convert to specific time zone
SELECT to_timestamp(1609459200) AT TIME ZONE 'America/New_York';
-- Get current timestamp in different time zones
SELECT NOW() AT TIME ZONE 'UTC' AS utc_time,
NOW() AT TIME ZONE 'America/New_York' AS ny_time;PostgreSQL excels at handling time zones. Use AT TIME ZONE to convert between time zones.
Formatting Timestamps
-- Format timestamp using to_char
SELECT to_char(to_timestamp(1609459200), 'YYYY-MM-DD HH24:MI:SS') AS formatted;
-- Format with timezone
SELECT to_char(to_timestamp(1609459200) AT TIME ZONE 'UTC',
'YYYY-MM-DD HH24:MI:SS TZ') AS formatted_with_tz;
-- ISO 8601 format
SELECT to_timestamp(1609459200)::text AS iso_format;Use to_char() function with format strings to customize timestamp output.
Working with Intervals
-- Extract epoch from interval
SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours 2 minutes');
-- Add interval to timestamp
SELECT to_timestamp(1609459200) + INTERVAL '1 day';
-- Subtract interval from timestamp
SELECT to_timestamp(1609459200) - INTERVAL '1 hour';
-- Calculate difference between timestamps
SELECT EXTRACT(EPOCH FROM (NOW() - to_timestamp(1609459200))) AS seconds_diff;PostgreSQL supports interval arithmetic for date/time calculations.
About PostgreSQL Unix Timestamps
PostgreSQL provides excellent support for Unix timestamps through the EXTRACT(EPOCH FROM ...) function and the to_timestamp() function. PostgreSQL handles time zones elegantly and provides precise timestamp operations. This guide covers all essential timestamp operations in PostgreSQL, including getting current timestamps, converting between timestamps and dates, and working with timezone-aware operations.
Related guides: Check out our guides for other databases: MySQL, and programming languages: JavaScript, Python, and more. For timestamp conversion tools, visit our Tools page.
Frequently Asked Questions
How do I get the current Unix timestamp in PostgreSQL?
Use EXTRACT(EPOCH FROM NOW()) to get the current Unix timestamp in seconds. PostgreSQL handles timezone conversions elegantly and provides precise timestamp operations.
How do I convert a Unix timestamp to a date in PostgreSQL?
Use to_timestamp(timestamp) to convert a Unix timestamp to a timestamp with time zone. You can also use TO_TIMESTAMP() with format strings for more control over the conversion.
What is the difference between EXTRACT(EPOCH FROM ...) and other timestamp functions?
EXTRACT(EPOCH FROM ...) extracts the Unix timestamp (seconds since epoch) from a timestamp value, while to_timestamp() converts a Unix timestamp to a timestamp. Use EXTRACT for getting timestamps, and to_timestamp for converting them.