LinLog

Yes, this is yet another "reminder to myself" Postgres post, because I've had to look this up at least 3 times.

To turn on expanded query output formatting in psql, the Postgres command-line client, run either \pset expanded or simply \x. They both do the same thing - make Postgres output each column in a result set as a separate line. This is equivalent to the mysql command-line client trick of ending the query with \G instead of a semi-colon.

Incidentally both of the above commands are boolean toggles. You run them once to turn on expanded formatting and run them again to turn it off.

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';