Postgresql: how to convert from Unix epoch to date?

Different flavors of epoch time

Seconds since the Big Bang? Not really. Unix epoch in seconds.

Most commonly, Unix epoch is represented as number of seconds. If you have a Unix timestamp in this signature style, pair it with TO_TIMESTAMP:

SELECT TO_TIMESTAMP(1617187800); -- Back to the Future! This brings you to TIMESTAMP '2021-03-31 02:30:00+00'


Milliseconds? Yes, that's also a thing. Unix epoch in milliseconds.

If milliseconds are your flavor, don't forget to share it by 1000 to convert to seconds, like so:

SELECT TO_TIMESTAMP(epoch_value / 1000);


Just replace epoch_value with your millisecond epoch timepiece:

SELECT TO_TIMESTAMP(1617187800000 / 1000); -- Here you are, right on time, in seconds


When accuracy matters. Time zones.

Trading with time zones can feel like crossing a busy motorway. For pin-point local times, take the following pattern as your guide:

SELECT (TIMESTAMP WITH TIME ZONE 'epoch' + epoch_value * INTERVAL '1 second') AT TIME ZONE 'UTC'


Advanced maneuvers with conversions

Can I change the Date?

You certainly can, brave coder! Want to move to the next date as if you've got a time machine? Just add an interval:

SELECT (TO_TIMESTAMP(epoch_value) + INTERVAL '1 day')::date;


Interval Training

You can flex extract(epoch from ...) for efficient interval workouts:

SELECT 'epoch' + (extract(epoch from now()) * interval '1 second') as exact_time_now;


Road bumps you might hit and how to steer clear

Overflow? Overflow.

There could be overflow issues when dealing with Unix epoch in milliseconds. Stick to this failsafe casting method:

SELECT TO_TIMESTAMP(CAST(epoch_value as bigint) / 1000);


What's your version?

PostgreSQL 13 cooks up new epoch conversion recipes. So always remember to use the right flavor for your database version.

Test drive always helps!

Always run your conversion queries for a test drive in your own application's neighborhood to ensure accuracy and reliability.

Just the Date, Please!

When your eyes desire clarity and all they want is the date part, serve them just that:

SELECT TO_TIMESTAMP(epoch_value)::date as just_the_date;


Optimize conversions for performance and readability

Keeping it short and sweet

Shorter statements often make your SQL easy on the eyes:

SELECT TO_TIMESTAMP(epoch_value)::date as conversion_result;


A well-named result is poor-man's comment, it lightens the cognitive load on the readers.

Know what you're dealing with

Understand the data types in play to ensure precision. It's the difference between getting the date right and hosting a birthday party a day early!

Change is the only constant

Create adaptable code. PostgreSQL changes with each version. Future-proof your queries by staying updated on new approaches.