postgresql timestamp with timezone to date

There's NEVER any confusion over which timezone you mean to have associated with a unix/epoch seconds time. They have a number of properties that make them the timestamp of choice in many database applications just because of the sheer number of headaches they avoid. SELECT date_part('epoch',CURRENT_TIMESTAMP)::intįor what it's worth unix timestamps, while not readily readable, are hardly 'goofy' or not 'rich'.

To convert back to unix timestamp you can use date_part: In newer versions to_timestamp is shorter and easier:

TIMESTAMP 'epoch' + goof.stupid_timestamp * INTERVAL '1 second' as ah_real_datetime We can create a view that shields us from this mess by doing the followingĬREATE OR REPLACE VIEW vwno_longer_goofy AS Now lets say we had a table of said goofy numbers and we prefer real date structures. We can convert it to a real date time simply by doing this: SELECT TIMESTAMP 'epoch' + 1195374767 * INTERVAL '1 second'. Lets say someone handed us a unix timestamp of the form 1195374767. PostgreSQL has lots of nice date time functions to perform these great feats.

Question: How do you convert this goofy seconds elapsed since Januto a real date? 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. The Unix Timestamp format in short is the number of seconds elapse since january 1, 1970. A lot of databases structures people setup seem to store dates using the Unix Timestamp format (AKA Unix Epoch).

Postgresql timestamp with timezone to date