Re: Grouping by date range

On Tue, 2003-08-19 at 02:56, Alexander Litvinov wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> I home your date field have date type. If it is try this:
>
> select date_part('year', date), count(*) from your_table group by
> date_part('year', date) order by date_part('year', date);

Is the ORDER BY really needed here?

> for month add grouping by date_part('month', date)
>
> if you need to handle large number of rows try to add columns with year and
> month, write triggers for filling this columns, make indexes and things
> should be fast.
>
> > date | data
> > ---------------
> > 01/01/01| 123
> > 01/01/01| abc
> > 02/01/01| def
> > 03/03/01| hij
> >
> > I can see how to group by day - but how do i go about decreasing the
> > precision down to months/years.

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron(dot)l(dot)johnson(at)cox(dot)net
Jefferson, LA USA

"My advice to you is to get married: If you find a good wife,
you will be happy; if not, you will become a philosopher."
Socrates