Re: Grouping by date range

Mat wrote:
> Hi,

> I have a table with two column, date and data
> I would like to do a set of queries to generate statistics on the data,
> such as count(data) for month blocks and year blocks. What is the best
> way to accomplish this?
>
>
> dd/mm/yy
> date | data
> ---------------
> 01/01/01| 123
> 01/01/01| abc
> 02/01/01| def
> 03/03/01| hij
>
> SOME QUERY ....
>
> Year | Count
> -------------
> 01 | 3
>
> I can see how to group by day - but how do i go about decreasing the
> precision down to months/years.

SELECT COUNT(*)
FROM mytable
GROUP BY date_trunc('month', date);

See:

http://www.postgresql.org/docs/7.3/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

for details.

Hope that helps,

Mike Mascari
mascarm(at)mascari(dot)com