Often in PostgreSQL you may want to convert an epoch to a readable date.
You can use the following basic syntax to do so:
SELECT *,
to_timestamp(epoch_date)::date AS readable_date
FROM sales;
This particular example will convert the epoch in the epoch_date column to a human readable date and save the result to a new column named readable_date.
Note: We use the syntax ::date to cast the result to a date format. Feel free to leave this out if you’d like the result to be a timestamp instead.
The following example shows how to use this syntax in practice.
Example: How to Convert Epoch to Date in PostgreSQL
Suppose that we create the following table named sales that contains information about the number of sales made by a store during specific epoch dates:
-- create table named 'sales'
CREATE TABLE sales (
epoch_date INTEGER,
amount INTEGER
);
-- add rows to the table
INSERT INTO sales VALUES (1717080469, 25);
INSERT INTO sales VALUES (1718480428, 44);
INSERT INTO sales VALUES (1717080405, 17);
INSERT INTO sales VALUES (1720080423, 23);
INSERT INTO sales VALUES (1724060969, 30);
INSERT INTO sales VALUES (1727580237, 11);
-- view all rows in table
SELECT * FROM sales;
epoch_date | amount
------------+--------
1717080469 | 25
1718480428 | 44
1717080405 | 17
1720080423 | 23
1724060969 | 30
1727580237 | 11
Suppose that we would like to convert each epoch in the epoch_date column to a human readable date.
We can use the following syntax to do so:
SELECT *,
to_timestamp(epoch_date)::date AS readable_date
FROM sales;
epoch_date | amount | readable_date
------------+--------+---------------
1717080469 | 25 | 2024-05-30
1718480428 | 44 | 2024-06-15
1717080405 | 17 | 2024-05-30
1720080423 | 23 | 2024-07-04
1724060969 | 30 | 2024-08-19
1727580237 | 11 | 2024-09-29
The new column named readable_date contains the date from the epoch_date column in a human readable format.
Feel free to use similar syntax to convert an epoch to a readable date in your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Select Rows in Date Range
PostgreSQL: How to Subtract Months from a Date
PostgreSQL: How to Generate Date Series Between Two Dates
