Print out timestamp as number, not human readable

Question

I need a command line that runs against psql and does:

psql database -c "select date_trunc('day', start) as date, avg(finish - start) as duration from things group by date order by date;"

But the problem is this gives me human readable output, aka:

    date         |    duration
---------------------+-----------------
 2010-01-11 00:00:00 | 00:24:13.826822
 2010-01-12 00:00:00 | 00:15:42.984878
 2010-01-13 00:00:00 | 00:15:37.117834
 2010-01-14 00:00:00 | 00:13:27.868552
 2010-01-15 00:00:00 | 00:09:16.018057
 2010-01-16 00:00:00 | 00:06:39.213929

What I want are unix timestamps in seconds (or milliseconds) as integers on both sides. I've read documentation but can't figure this out.

Answer 1

I read that you can do it with

SELECT date_part('epoch', now()) 

give unix timestamp

So in your case

SELECT date_part('epoch', date_trunc('day', start) as date), 
       date_part('epoch', avg(finish - start)) as duration 
FROM things 
GROUP by date 
ORDER by date;

See Date and time functions and operations in postgres