PostgreSQL: Convert Date to Unix Timestamp

You need to work with Unix timestamps — the number of seconds since January 1, 1970. Maybe you’re storing data for APIs, working with system logs, or integrating with other platforms that use epoch time. Converting from PostgreSQL dates is simple once you know the function.

Quick Answer: Use EXTRACT(EPOCH FROM …)

To convert a date or timestamp to Unix timestamp, use the EXTRACT(EPOCH FROM ...) function:

SELECT
    emp_id,
    first_name,
    hire_date,
    EXTRACT(EPOCH FROM hire_date)::BIGINT as hire_unix_timestamp,
    EXTRACT(EPOCH FROM CURRENT_DATE)::BIGINT as today_unix
FROM employees
WHERE hire_date IS NOT NULL
LIMIT 5;

Returns:

emp_id | first_name | hire_date  | hire_unix_timestamp | today_unix
-------+------------+------------+---------------------+------------------
     1 | James      | 2020-01-15 |          1579046400 |  1771545045
     2 | Mary       | 2019-06-20 |          1560940800 |  1771545045

That’s it. EXTRACT(EPOCH FROM date) gives you seconds since 1970-01-01 UTC.

How It Works

Unix timestamp (epoch time) is a standard way to represent time as a single number: seconds elapsed since January 1, 1970 00:00:00 UTC. PostgreSQL’s EXTRACT(EPOCH FROM ...) calculates exactly this.

When you EXTRACT(EPOCH FROM hire_date), PostgreSQL computes the number of seconds between that date and the epoch. Cast it to BIGINT if you want a whole number without decimals.

Different Approaches & When to Use Them

Approach 1: Simple Conversion (Most Common)

Convert a date or timestamp directly:

SELECT
    EXTRACT(EPOCH FROM CURRENT_DATE)::BIGINT as today_epoch,
    EXTRACT(EPOCH FROM NOW())::BIGINT as now_epoch,
    EXTRACT(EPOCH FROM hire_date)::BIGINT as hire_epoch
FROM employees;

Works with dates, timestamps, and timezone-aware timestamps.

Best for: Standard epoch conversions.

Approach 2: With Precision (Microseconds)

If you need sub-second precision, don’t cast to BIGINT:

SELECT
    hire_date,
    EXTRACT(EPOCH FROM hire_date) as epoch_with_decimals,
    ROUND(EXTRACT(EPOCH FROM hire_date), 2) as epoch_rounded
FROM employees;

This preserves microsecond precision in the result.

Best for: Applications needing high-precision timestamps.

Approach 3: Milliseconds Instead of Seconds

For JavaScript and some APIs that use milliseconds:

SELECT
    EXTRACT(EPOCH FROM hire_date) as epoch_seconds,
    (EXTRACT(EPOCH FROM hire_date) * 1000)::BIGINT as epoch_milliseconds
FROM employees;

Many modern APIs expect milliseconds instead of seconds.

Best for: Web applications and APIs.

Approach 4: Difference Between Dates (In Seconds)

Calculate how many seconds elapsed between two dates:

SELECT
    emp_id,
    hire_date,
    EXTRACT(EPOCH FROM (CURRENT_DATE - hire_date))::BIGINT as seconds_employed,
    ROUND(EXTRACT(EPOCH FROM (CURRENT_DATE - hire_date)) / 86400) as days_employed
FROM employees
WHERE hire_date IS NOT NULL
ORDER BY days_employed DESC;

Divide by 86400 (seconds per day) to convert to days.

Best for: Duration calculations.

Approach 5: With Timezone Handling

For timezone-aware timestamps, specify the timezone:

SELECT
    EXTRACT(EPOCH FROM created_at AT TIME ZONE 'UTC')::BIGINT as epoch_utc,
    EXTRACT(EPOCH FROM created_at AT TIME ZONE 'US/Eastern')::BIGINT as epoch_eastern
FROM events;

Both return the same epoch (since epoch is always UTC-based), but this shows your intent.

Real-World Example: Event Timeline

Create an event timeline where you need both human-readable dates and Unix timestamps for APIs:

SELECT
    event_id,
    event_name,
    event_date,
    EXTRACT(EPOCH FROM event_date)::BIGINT as event_timestamp,
    EXTRACT(EPOCH FROM CURRENT_DATE)::BIGINT as current_timestamp,
    EXTRACT(EPOCH FROM event_date)::BIGINT - EXTRACT(EPOCH FROM CURRENT_DATE)::BIGINT as seconds_until_event
FROM events
WHERE event_date > CURRENT_DATE
ORDER BY event_date;

Now you can display both human-readable dates on your website and Unix timestamps in API responses.

Use Cases for Unix Timestamps

  • API responses: Many REST APIs use epoch time
  • Database storage: Some systems store time as integers for speed
  • Caching: Unix timestamps are compact and easy to work with
  • System logs: Linux/Unix systems use epoch time natively
  • JavaScript: Frontend code often works with millisecond epochs

Performance Notes

EXTRACT(EPOCH FROM ...) is very fast — essentially a math operation. No indexes or special optimization needed. Use it freely in queries.

FAQ

Q: What’s the difference between storing dates vs. Unix timestamps? A: Dates are human-readable and timezone-aware. Timestamps are compact numbers. Use dates for storage, convert to epoch for APIs and special use cases.

Q: How do I convert back from Unix timestamp to date? A: Use TO_TIMESTAMP(epoch_value) or TIMESTAMP 'epoch' + (seconds * '1 second'::INTERVAL).

Q: Does epoch calculation account for leap seconds? A: No, Unix time doesn’t. Epoch is a simple count of seconds ignoring leap seconds.

Q: Can I compare dates by converting to epoch? A: You can, but it’s slower than comparing dates directly. Keep comparisons in date format.

Q: Why cast to BIGINT instead of keeping decimals? A: APIs usually expect integers. Decimals are useful for precise calculations but harder for APIs to handle.

Q: What about dates before 1970? A: EXTRACT(EPOCH FROM …) returns negative numbers for dates before 1970-01-01.

Q: Is 1704067200 the same timestamp everywhere? A: Yes. Epoch is UTC-based, so a Unix timestamp represents the same instant worldwide, regardless of timezone.

Wrapping Up

Converting dates to Unix timestamps in PostgreSQL is a one-liner: EXTRACT(EPOCH FROM date)::BIGINT. Use it when you need to export data to APIs, work with system logs, or integrate with platforms expecting epoch time.

The key: epoch is always in UTC and always represents the same instant worldwide. Perfect for consistent, portable timestamp representation.