Convert Unix Timestamp to Date in PostgreSQL

Overview

A Unix timestamp (also called epoch time) represents the number of seconds that have elapsed since 00:00:00 UTC on 1 January 1970. PostgreSQL, however, stores calendar dates and times in rich, strongly-typed columns such as DATE, TIMESTAMP, and TIMESTAMPTZ. Converting between these formats is a common task in data engineering pipelines, application back-ends, and ad-hoc analytics.

Why You Need to Convert Unix Timestamps

• Human readability: Epoch integers are meaningless to most analysts and business stakeholders.
• Time-zone handling: PostgreSQL’s TIMESTAMPTZ makes offsets explicit, reducing logic errors.
• Indexing and performance: Native timestamp types are more efficient for range filters and partition pruning.
• SQL functions: Date math (e.g., date_trunc, extract) only works on timestamp types.

Unix Epoch 101

What Exactly Is Stored?

An epoch value is usually a 32-bit or 64-bit integer counting seconds (or sometimes milliseconds) from the epoch. Because it is relative, it has no intrinsic time-zone metadata; you must know whether the source system recorded it as UTC or local time.

PostgreSQL Time Types at a Glance

• DATE – calendar date with no time component.
• TIMESTAMP – date + time without offset; interpreted in the session’s current time zone.
• TIMESTAMPTZ – date + time + time-zone offset; stored internally in UTC.

Core Conversion Techniques

1. Using to_timestamp()

SELECT to_timestamp(1685577600);
-- returns 2023-06-01 00:00:00+00

to_timestamp() expects epoch seconds (or double precision seconds). The default result is a TIMESTAMPTZ in the session’s time zone.

2. Adjusting Time Zone with AT TIME ZONE

SELECT to_timestamp(1685577600) AT TIME ZONE 'America/Los_Angeles';
-- 2023-05-31 17:00:00

AT TIME ZONE converts a TIMESTAMPTZ to a plain TIMESTAMP relative to the specified zone.

3. Milliseconds to Timestamp

SELECT to_timestamp(epoch_millis / 1000.0);


Be sure to divide by 1000.0 so PostgreSQL treats the value as double precision. A common alternative is using integer division and then adding make_interval(), but floating-point is simpler and performant for most use cases.

4. Casting to DATE

SELECT to_timestamp(1685577600)::date;
-- 2023-06-01

Once you have a TIMESTAMP, you can cast directly to DATE if you only need the calendar day.

Working Examples

Bulk Conversion in an UPDATE

ALTER TABLE events ADD COLUMN event_ts TIMESTAMPTZ;

UPDATE events
SET event_ts = to_timestamp(epoch_seconds)
WHERE event_ts IS NULL;


Querying with Parameterized Filters

SELECT *
FROM page_views
WHERE view_time > to_timestamp($1) -- $1 passed in as epoch seconds
AND user_id = $2;


Best Practices

• Store time zones explicitly: Prefer TIMESTAMPTZ over TIMESTAMP for long-term storage.
• Normalize to UTC: Convert input to UTC immediately, then display in local time on the client side.
• Validate precision: Know whether incoming data is seconds, milliseconds, or microseconds.
• Index converted columns: Range queries on TIMESTAMPTZ using B-tree or BRIN indexes perform far better than on raw integers.

Common Mistakes and How to Avoid Them

1. Confusing Seconds with Milliseconds

Why it’s wrong: Passing milliseconds directly to to_timestamp() inflates the result by 1,000×.
Fix: Divide by 1000.0 or use integer arithmetic (epoch_ms / 1000).

2. Ignoring Session Time Zone

Why it’s wrong: to_timestamp() returns a timestamp in the current session zone, leading to inconsistent results across connections.
Fix: Immediately chain AT TIME ZONE 'UTC' (or your canonical zone) after to_timestamp(), or set SET TIME ZONE 'UTC' at the start of the session.

3. Storing as TIMESTAMP Without Offset

Why it’s wrong: Dropping the offset makes daylight-saving conversions and cross-region analytics error-prone.
Fix: Use TIMESTAMPTZ or keep the raw epoch and convert on read.

Galaxy in Your Workflow

If you are using the Galaxy SQL editor, converting Unix timestamps is even easier:

1. Type to_timestamp( and Galaxy’s AI copilot autocompletes the signature and warns if you feed it milliseconds.
2. Highlight a raw epoch column in your query; choose Quick Fix → Convert to TIMESTAMPTZ. Galaxy rewrites the SQL with to_timestamp() and safe casting.
3. Add the finished query to a Collection, so teammates see an endorsed, canonical approach.

Conclusion

Unix timestamps are ubiquitous in log files and APIs, but PostgreSQL’s rich time types unlock advanced querying, easier debugging, and better performance. By mastering to_timestamp(), AT TIME ZONE, and careful time-zone management, you can move seamlessly between raw epoch data and business-friendly dates.