Postgres Pro
Downloads
Home   >   mailing lists

Thread: Grouping by date range

Grouping by date range

From
Mat
Date:
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.

Thanks...

Re: Grouping by date range

From
Mike Mascari
Date:
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@mascari.com

Re: Grouping by date range

From
Alexander Litvinov
Date:
-----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);

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.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQE/QdhAV+WKOINIfOYRAhT6AJ42zbMyux2CLLJh1XvAtYBrJhkhNwCfZXH5
AQH6c+qKqwbFZT3yNdTcm5I=
=tmYH
-----END PGP SIGNATURE-----

Re: Grouping by date range

From
Ron Johnson
Date:
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.l.johnson@cox.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

Re: Grouping by date range

From
Jeffrey Melloy
Date:
Alexander Litvinov wrote:

>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.
>
>
Is this the only way to do it?  I was running into this problem, too.
It would be nice if the function indexes could handle things like
'date_part('month', <columname>)

Jeff

Re: Grouping by date range

From
Alvaro Herrera
Date:
On Tue, Aug 19, 2003 at 12:07:57PM -0500, Jeffrey Melloy wrote:
> Alexander Litvinov wrote:
>
> >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.
>
> Is this the only way to do it?  I was running into this problem, too.
> It would be nice if the function indexes could handle things like
> 'date_part('month', <columname>)

That's why they can in the upcoming 7.4...  In the meantime I think you
can create an index using a function that has the constants inline, i.e.
a function date_part_month(), etc.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Es filosofo el que disfruta con los enigmas" (G. Coli)

Re: Grouping by date range

From
Tom Lane
Date:
Ron Johnson <ron.l.johnson@cox.net> writes:
> On Tue, 2003-08-19 at 02:56, Alexander Litvinov wrote:
>> 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?

If you want the results ordered that way, yes.

            regards, tom lane

Re: Grouping by date range

From
Ron Johnson
Date:
On Wed, 2003-08-20 at 12:47, Tom Lane wrote:
> Ron Johnson <ron.l.johnson@cox.net> writes:
> > On Tue, 2003-08-19 at 02:56, Alexander Litvinov wrote:
> >> 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?
>
> If you want the results ordered that way, yes.

Hmmmmm.  I don't think so, if the ORDER BY clause is exactly the
same as the GROUP BY clause, which is the case here:
  select date_part('year', date), count(*)
  from your_table
  group by date_part('year', date)
  order by date_part('year', date);

The GROUP BY does implicit sorting, so an ORDER BY on the exact same
column(s) as the GROUP BY is redundant.

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"Adventure is a sign of incompetence"
Stephanson, great polar explorer

Re: Grouping by date range

From
Bruno Wolff III
Date:
On Wed, Aug 20, 2003 at 13:44:59 -0500,
  Ron Johnson <ron.l.johnson@cox.net> wrote:
>
> The GROUP BY does implicit sorting, so an ORDER BY on the exact same
> column(s) as the GROUP BY is redundant.

That is an implementation detail, not a promise. With hashed aggregates
in 7.4, you might find this isn't true.

Re: Grouping by date range

From
Ron Johnson
Date:
On Wed, 2003-08-20 at 13:58, Bruno Wolff III wrote:
> On Wed, Aug 20, 2003 at 13:44:59 -0500,
>   Ron Johnson <ron.l.johnson@cox.net> wrote:
> >
> > The GROUP BY does implicit sorting, so an ORDER BY on the exact same
> > column(s) as the GROUP BY is redundant.
>
> That is an implementation detail, not a promise. With hashed aggregates
> in 7.4, you might find this isn't true.

Now that's interesting.  I'd have gone to my grave thinking it was
part of the spec...

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"As I like to joke, I may have invented it, but Microsoft made
it popular"
David Bradley, regarding Ctrl-Alt-Del

Re: Grouping by date range

From
Bruno Wolff III
Date:
On Wed, Aug 20, 2003 at 14:02:59 -0500,
  Ron Johnson <ron.l.johnson@cox.net> wrote:
> On Wed, 2003-08-20 at 13:58, Bruno Wolff III wrote:
> > On Wed, Aug 20, 2003 at 13:44:59 -0500,
> >   Ron Johnson <ron.l.johnson@cox.net> wrote:
> > >
> > > The GROUP BY does implicit sorting, so an ORDER BY on the exact same
> > > column(s) as the GROUP BY is redundant.
> >
> > That is an implementation detail, not a promise. With hashed aggregates
> > in 7.4, you might find this isn't true.
>
> Now that's interesting.  I'd have gone to my grave thinking it was
> part of the spec...

I just tried something out quick and a select with group by didn't
return the data in ascending order. (This is on CVS from about a week ago.)

bruno=> create table temp (col int);
CREATE TABLE
bruno=> insert into table values (3);
ERROR:  syntax error at or near "table" at character 13
bruno=> insert into temp values (3);
INSERT 182888 1
bruno=> insert into temp values (1);
INSERT 182889 1
bruno=> insert into temp values (2);
INSERT 182890 1
bruno=> analyze temp;
ANALYZE
bruno=> select * from temp group by col;
 col
-----
   3
   2
   1
(3 rows)

Re: Grouping by date range

From
Tom Lane
Date:
Bruno Wolff III <bruno@wolff.to> writes:
> On Wed, Aug 20, 2003 at 13:44:59 -0500,
>   Ron Johnson <ron.l.johnson@cox.net> wrote:
>> The GROUP BY does implicit sorting, so an ORDER BY on the exact same
>> column(s) as the GROUP BY is redundant.

> That is an implementation detail, not a promise. With hashed aggregates
> in 7.4, you might find this isn't true.

s/might/will/

            regards, tom lane

Re: Grouping by date range

From
Ron Johnson
Date:
On Wed, 2003-08-20 at 14:51, Tom Lane wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
> > On Wed, Aug 20, 2003 at 13:44:59 -0500,
> >   Ron Johnson <ron.l.johnson@cox.net> wrote:
> >> The GROUP BY does implicit sorting, so an ORDER BY on the exact same
> >> column(s) as the GROUP BY is redundant.
>
> > That is an implementation detail, not a promise. With hashed aggregates
> > in 7.4, you might find this isn't true.
>
> s/might/will/


From 7.3.3, where the records were randomly inserted; note how
GROUP BY acts like I described:

test1=# select f, count(*)
test1-# from t
test1-# group by f;
f | count
---+-------
1 |     3
2 |     5
4 |     4
(3 rows)

The new 7.4 attitude is *really* good to know, because, otherwise,
all our reports would break!


--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"Fair is where you take your cows to be judged."
Unknown