PostgreSQL Epoch Converter
Convert between Unix epoch timestamps and PostgreSQL dates. Generate to_timestamp() and extract(epoch) SQL queries.
Client-side only — nothing leaves your browser
Auto-detected: seconds · 3.0y ago
Conversions
Epoch (seconds)
1679616000
Epoch (milliseconds)
1679616000000
Human Readable
Friday, March 24, 2023 at 07:00:00 AM GMT+7
ISO 8601
2023-03-24T00:00:00.000Z
PostgreSQL Timestamp
2023-03-24 00:00:00.000+00
PostgreSQL SQL
Epoch to timestamp
SELECT to_timestamp(1679616000);Epoch ms to timestamp
SELECT to_timestamp(1679616000000 / 1000.0);With timezone
SELECT to_timestamp(1679616000) AT TIME ZONE 'UTC';Format as text
SELECT to_char(to_timestamp(1679616000), 'YYYY-MM-DD HH24:MI:SS');PostgreSQL Epoch & Timestamps
PostgreSQL uses to_timestamp() to convert Unix epoch seconds to a timestamptz. To go the other direction, use extract(epoch FROM timestamp). PostgreSQL internally stores timestamps as microseconds since 2000-01-01, but converts to/from Unix epoch (1970-01-01) automatically.
Quick Reference
- to_timestamp(epoch) — Epoch seconds → timestamptz
- extract(epoch FROM ts) — Timestamp → epoch seconds
- AT TIME ZONE — Convert between timezones
- to_char(ts, fmt) — Format timestamp as text
- now() — Current timestamp with timezone
Notable Epoch Values
| Event | Epoch (seconds) | Date |
|---|---|---|
| Unix Epoch | 0 | January 1, 1970 00:00:00 UTC |
| 1 Billion | 1000000000 | September 9, 2001 01:46:40 UTC |
| Y2K38 (32-bit limit) | 2147483647 | January 19, 2038 03:14:07 UTC |
| 2 Billion | 2000000000 | May 18, 2033 03:33:20 UTC |
| Current (approx) | ~1774000000 | March 2026 |
Frequently Asked Questions
What is the Unix epoch?
The Unix epoch is January 1, 1970 at 00:00:00 UTC. Unix timestamps count the number of seconds (or milliseconds) that have elapsed since this date. It is the standard time reference for most programming languages, databases, and operating systems. PostgreSQL uses this same epoch for its timestamp calculations.
What is extract(epoch) in PostgreSQL?
extract(epoch FROM ...) is a PostgreSQL function that returns the number of seconds since the Unix epoch (January 1, 1970) for a given timestamp or interval. For timestamps, it returns the total seconds: SELECT extract(epoch FROM now()); For intervals, it returns the interval's total duration in seconds: SELECT extract(epoch FROM interval '1 day 2 hours'); — returns 93600. The result is a double precision number; cast to ::bigint if you need an integer.
What is the difference between epoch seconds and milliseconds?
Unix epoch in seconds is a 10-digit number (e.g., 1679616000). Epoch in milliseconds is a 13-digit number (e.g., 1679616000000) — 1000x larger. JavaScript's Date.now() returns milliseconds, while PostgreSQL's extract(epoch) returns seconds. This tool auto-detects the format based on digit count.
What data type should I use to store epoch time in PostgreSQL?
PostgreSQL offers two main approaches: store epoch values as bigint (integer seconds or milliseconds), or use the native timestamptz type. timestamptz is generally preferred because PostgreSQL handles timezone conversions, comparisons, and date arithmetic natively. If you receive epoch integers from an API, convert on insert: INSERT INTO events(created_at) VALUES (to_timestamp(1679616000)); If you must store raw epoch values, use bigint (not integer) to avoid the 2038 overflow limit of 32-bit integers.
How do I convert epoch to a specific timezone in PostgreSQL?
Use to_timestamp() with AT TIME ZONE: SELECT to_timestamp(1679616000) AT TIME ZONE 'America/New_York'; This converts the epoch to a timestamp in the specified timezone. PostgreSQL supports IANA timezone names (America/New_York, Europe/London, Asia/Tokyo, etc.).
Need a PostgreSQL GUI Client?
1bench is a modern database client for PostgreSQL and 20+ other databases. Query, browse, and manage your data visually.
Learn More