In PostgreSQL, we can use the to_timestamp() function to convert a Unix timestamp value to a date/time value.

The Unix timestamp (also known as Unix Epoch time, Unix time, or POSIX time) is the number of seconds that have elapsed since 00:00:00 Thursday, 1 January 1970, Coordinated Universal Time (UTC).

Example

Here’s a simple example to demonstrate:

SELECT to_timestamp(1912995045);

Result:

2030-08-15 03:30:45+00

In this case I passed a specific Unix timestamp value.

Here’s another example that further demonstrates how the result reflects the Unix timestamp value provided:

SELECT 
    now() AS "Current Date/Time",
    extract(epoch from now()) AS "Unix Timestamp",
    to_timestamp(extract(epoch from now())) AS "And back again...";

Result:

       Current Date/Time       |  Unix Timestamp   |       And back again...       
-------------------------------+-------------------+-------------------------------
 2022-04-19 19:25:27.068737+00 | 1650396327.068737 | 2022-04-19 19:25:27.068737+00

In this example we used now() to output the current date and time. We then used extract() to get the Unix timestamp from that date and time value. Finally, we used to_timestamp() to convert it back to the original date and time value.

Convert to Date

We can also cast the result as a date value to eliminate the time part:

SELECT to_timestamp(1912995045)::date;

Result:

2030-08-15