Timestamps in PostgreSQL

So at my new job (maybe I'll post something about that later), we use PostgreSQL for our RDBMS. And, for reasons I won't get into, our application stores dates as UNIX timestamps, rather than, you know, actual dates. Thus this quick note to myself so I don't have to keep looking up how to convert between the two.

Date to UNIX timestamp in Postgres (from this page):
select date_part('epoch',now()) as unixtime; -- This will do it.
select extract('epoch' from now()) as unixtime; -- ... and so will this.

UNIX timestamp to Postgres TIMESTAMP (from this page):
SELECT TIMESTAMP 'epoch' + 1195374767 * INTERVAL '1 second';

You can reply to this entry by leaving a comment below. This entry accepts Pingbacks from other blogs. You can follow comments on this entry by subscribing to the RSS feed.

Add your comments #

A comment body is required. No HTML code allowed. URLs starting with http:// or ftp:// will be automatically converted to hyperlinks.