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. You can send TrackBack pings to this URL. 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.