PostgreSQL - GROUP BY timestamp values?

Question

I've got a table with purchase orders stored in it. Each row has a timestamp indicating when the order was placed. I'd like to be able to create a report indicating the number of purchases each day, month, or year. I figured I would do a simple SELECT COUNT(xxx) FROM tbl_orders GROUP BY tbl_orders.purchase_time and get the value, but it turns out I can't GROUP BY a timestamp column.

Is there another way to accomplish this? I'd ideally like a flexible solution so I could use whatever timeframe I needed (hourly, monthly, weekly, etc.) Thanks for any suggestions you can give!

Answer 1

This does the trick without the date_trunc function (easier to read).

// 2014 select created_on::DATE from users group by created_on::DATE  // updated September 2018 (thanks to @wegry) select created_on::DATE as co from users group by co 

What we're doing here is casting the original value into a DATE rendering the time data in this value inconsequential.