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?
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
UPDATE - 2010-08-21 as noted by a user below. In newer versions to_timestamp is shorter and easier:
To convert back to unix timestamp you can use date_part:
For what it's worth unix timestamps, while not readily readable, are hardly "goofy" or not "rich". 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. Desirable properties include:
- There's NEVER any confusion over which timezone you mean to have associated with a unix/epoch seconds time. There's no such thing and nothing extra to try to drag along or accidentally omit.
- The epoch seconds value for every location on earth at a given moment is identical. This is incredibly useful as soon as you start recording data from multiple timezones, or if you ever move data around.
- Calculating time deltas between timestamps is trivial.
- Unix time/epoch seconds value can be "rendered" to whichever locale is desired. Timezone, DST and all the headaches that go with tracking them become display time details...the backend data will always be correct and not need updates everytime someone changes their DST rules.