Converting from Unix Timestamp to PostgreSQL Timestamp or Date

A lot of databases structures people setup seem to store dates using the Unix Timestamp format (AKA Unix Epoch). The Unix Timestamp format in short is the number of seconds elapse since january 1, 1970. The PostgreSQL timestamp is a date time format (for those coming from SQL Server and MySQL) and of course is much richer than Unix Timestamp.

Question: How do you convert this goofy seconds elapsed since January 1, 1970 to a real date?

Answer:

PostgreSQL has lots of nice date time functions to perform these great feats. Lets say someone handed us a unix timestamp of the form 1195374767. We can convert it to a real date time simply by doing this: SELECT TIMESTAMP 'epoch' + 1195374767 * INTERVAL '1 second'.

Now lets say we had a table of said goofy numbers and we prefer real date structures. We can create a view that shields us from this mess by doing the following

CREATE OR REPLACE VIEW vwno_longer_goofy AS
SELECT goof.id, goof.stupid_timestamp, 
      TIMESTAMP 'epoch' + goof.stupid_timestamp * INTERVAL '1 second' as ah_real_datetime
FROM goof

UPDATE - 2010-08-21 as noted by a user below. In newer versions to_timestamp is shorter and easier:


SELECT to_timestamp(1195374767);

To convert back to unix timestamp you can use date_part:


SELECT date_part('epoch',CURRENT_TIMESTAMP)::int