Convert Unix Timestamp in PostgreSQL — to_timestamp and EXTRACT epoch

PostgreSQL has the cleanest timestamp story of any major database — but it has two timestamp types that look identical and behave very differently. timestamp stores no timezone. timestamptz stores a timezone-aware moment. Pick the wrong one and you have a bug nobody will catch until daylight savings.

This guide covers the conversion functions, the type difference, and the patterns that work in real production code.

Convert Unix Timestamp to a Date with to_timestamp

SELECT to_timestamp(1711000000);
-- 2024-03-21 07:46:40+00 (returns timestamptz in session timezone display)

-- With format
SELECT to_char(to_timestamp(1711000000), 'YYYY-MM-DD HH24:MI:SS');
-- 2024-03-21 07:46:40

SELECT to_char(to_timestamp(1711000000), 'Mon DD, YYYY');
-- Mar 21, 2024

to_timestamp returns a timestamptz (timestamp with time zone). The display in psql will be in your session timezone — change it with SET timezone = 'UTC'; if you want UTC output.

If your timestamp is in milliseconds, divide by 1000 first: to_timestamp(ts_ms / 1000.0). The decimal point matters — without it you get integer division which loses sub-second precision.

Get a Unix Timestamp with EXTRACT(epoch FROM ...)

-- Current Unix timestamp
SELECT EXTRACT(epoch FROM NOW())::bigint;
-- e.g. 1744070000

-- From a literal timestamp
SELECT EXTRACT(epoch FROM '2024-03-21 07:46:40+00'::timestamptz)::bigint;
-- 1711000000

-- From a column
SELECT EXTRACT(epoch FROM created_at)::bigint AS unix_ts FROM orders;

The cast to bigint at the end is important — EXTRACT returns a double precision number that includes fractional seconds. If you want seconds as an integer, cast it. For milliseconds: (EXTRACT(epoch FROM NOW()) * 1000)::bigint.

Sell Custom Apparel — We Handle Printing & Free Shipping

timestamp vs timestamptz — Pick the Right One

This is the most important distinction in PostgreSQL date handling. Get it wrong and you get bugs that only appear at daylight savings transitions or when the server moves zones.

Type	Stores	When to use
timestamp	Date and time, no zone info	Birthdays, business hours, anything conceptually local
timestamptz	An exact instant in UTC, displayed in session timezone	Event times, log entries, anything that happened at a specific moment

The names are confusing because timestamptz does NOT store the timezone — it stores the instant in UTC and converts to the session timezone for display. The "with time zone" suffix is historical and misleading.

Rule of thumb: default to timestamptz for almost everything. Use plain timestamp only when you specifically mean "this date and time in some unspecified location" (like a meeting that happens at 9 AM local time wherever the user is).

CURRENT_TIMESTAMP, NOW, and Default Column Values

-- All return the same timestamptz value
SELECT NOW();
SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_TIMESTAMP();

-- For the date only
SELECT CURRENT_DATE;

-- Default in CREATE TABLE
CREATE TABLE events (
    id serial PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT NOW(),
    updated_at timestamptz NOT NULL DEFAULT NOW()
);

-- For an updated_at column you need a trigger (Postgres has no ON UPDATE)
CREATE OR REPLACE FUNCTION set_updated_at() RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_events_updated
    BEFORE UPDATE ON events
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Unlike MySQL, PostgreSQL has no ON UPDATE clause for default columns. You need a trigger. It is more code but it gives you complete control over which updates trigger the timestamp change.

NOW vs statement_timestamp vs clock_timestamp

NOW() returns the start time of the current transaction — same value for every call within one transaction. clock_timestamp() returns the actual wall clock time at the moment of the call — different for every call. For most use cases NOW() is what you want.

For a quick value verification, the free Unix timestamp converter handles seconds and milliseconds.

Frequently Asked Questions

How do I convert a Unix timestamp to a date in PostgreSQL?

Use to_timestamp(timestamp_value). It returns a timestamptz that displays in the session's current timezone. To force UTC display, run SET timezone = "UTC" first or wrap with AT TIME ZONE "UTC".

How do I get a Unix timestamp from a date in PostgreSQL?

Use EXTRACT(epoch FROM your_timestamp)::bigint. This returns the Unix timestamp as an integer. For milliseconds, multiply by 1000 before casting: (EXTRACT(epoch FROM NOW()) * 1000)::bigint.

What is the difference between timestamp and timestamptz in PostgreSQL?

timestamp has no timezone information at all. timestamptz stores an exact instant in UTC and converts to the session timezone for display. Despite the name, timestamptz does NOT store the original timezone — it just stores a UTC moment. Default to timestamptz for almost everything.

How do I handle millisecond timestamps in PostgreSQL?

For a millisecond Unix timestamp, divide by 1000.0 (with the decimal point) before passing to to_timestamp: to_timestamp(ts_ms / 1000.0). The decimal preserves sub-second precision. Without it you get integer division and lose milliseconds.

Why does NOW() return the same value multiple times in a transaction?

PostgreSQL NOW() returns the start time of the current transaction, not the current wall clock time. This is intentional — it means all rows inserted in one transaction get identical timestamps. If you need the actual current wall clock, use clock_timestamp() instead.

How do I create an updated_at column that auto-updates in PostgreSQL?

PostgreSQL has no ON UPDATE clause like MySQL. You need a trigger function that sets NEW.updated_at = NOW() and a BEFORE UPDATE trigger on the table. It is more setup but gives you complete control over which updates trigger the timestamp change.