On Tue, Jul 15, 2008 at 6:12 AM, Oliveiros Cristina
<oliveiros.cristina@marktest.pt> wrote:
> Howdy, all,
>
> I have a problem.
>
> I have a table which one of the fields is of type date.
>
> I need to obtain the totals of the other fields in a by-month basis
> IS there any easy way to do this using the GROUP BY or any other construct?
>

In addition to the responses on grouping by extract('month' from
timestamp) you can also index on this function as long as timestamp
isn't timestamp with timezone. With that index in place, grouping by
month can be pretty fast even for large datasets covering many months.

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On Tue, 2008-07-15 at 14:31 +0200, A. Kretschmer wrote:
> am Tue, dem 15.07.2008, um 13:12:39 +0100 mailte Oliveiros Cristina folgendes:
> > Howdy, all,
> >
> > I have a problem.
> >
> > I have a table which one of the fields is of type date.
> >
> > I need to obtain the totals of the other fields in a by-month basis
> > IS there any easy way to do this using the GROUP BY or any other construct?
>
> ... group by extract(month from date)
>
>
> Andreas

It's worth noting that extract(month from timestamp) returns a month_no, and thus will not be suitable for grouping queries that span years.

I recommend group by date_trunc('month', <<timestamp field>>)

-Mark


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On Tue, Jul 15, 2008 at 7:15 PM, Mark Roberts
<mailing_lists@pandapocket.com> wrote:
>
> On Tue, 2008-07-15 at 14:31 +0200, A. Kretschmer wrote:
>> am Tue, dem 15.07.2008, um 13:12:39 +0100 mailte Oliveiros Cristina folgendes:
>> > Howdy, all,
>> >
>> > I have a problem.
>> >
>> > I have a table which one of the fields is of type date.
>> >
>> > I need to obtain the totals of the other fields in a by-month basis
>> > IS there any easy way to do this using the GROUP BY or any other construct?
>>
>> ... group by extract(month from date)
>>
>>
>> Andreas
>
> It's worth noting that extract(month from timestamp) returns a month_no, and thus will not be suitable for grouping queries that span years.
>
> I recommend group by date_trunc('month', <<timestamp field>>)

Both have their uses. If you're viewing the last 5 decembers versus
the last 5 novembers, then extract would be a good choice. But mostly
date_trunc is more useful.

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

am Tue, dem 15.07.2008, um 15:57:54 -0600 mailte Scott Marlowe folgendes:
> On Tue, Jul 15, 2008 at 6:12 AM, Oliveiros Cristina
> <oliveiros.cristina@marktest.pt> wrote:
> > Howdy, all,
> >
> > I have a problem.
> >
> > I have a table which one of the fields is of type date.
> >
> > I need to obtain the totals of the other fields in a by-month basis
> > IS there any easy way to do this using the GROUP BY or any other construct?
> >
>
> In addition to the responses on grouping by extract('month' from
> timestamp) you can also index on this function as long as timestamp
> isn't timestamp with timezone. With that index in place, grouping by

You can also create an index on an timestamptz column for a particular
time zone ;-)

test=*# create table foo (ts timestamptz);
CREATE TABLE
test=*# create index foo_idx on foo(extract(month from ts at time zone 'GMT'));
CREATE INDEX


Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

am Tue, dem 15.07.2008, um 18:15:07 -0700 mailte Mark Roberts folgendes:
>
> On Tue, 2008-07-15 at 14:31 +0200, A. Kretschmer wrote:
> > am Tue, dem 15.07.2008, um 13:12:39 +0100 mailte Oliveiros Cristina folgendes:
> > > Howdy, all,
> > >
> > > I have a problem.
> > >
> > > I have a table which one of the fields is of type date.
> > >
> > > I need to obtain the totals of the other fields in a by-month basis
> > > IS there any easy way to do this using the GROUP BY or any other construct?
> >
> > ... group by extract(month from date)
> >
> >
> > Andreas
>
> It's worth noting that extract(month from timestamp) returns a month_no, and thus will not be suitable for grouping queries that span years.

Right, but that wasn't the question...


Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

> No, they developed it for marketing.

Perhaps, but towards whom? PostgreSQL wouldn't hurt if a lot of developers and
DBA's was lured into the trap by this new feature.

> Keep in mind that Oracle has six thousand full-time developers and an
> already extremely mature database. Stuff that they see fit to add is
> not necessarily going to be on our radar screen in the foreseeable
> future.

I wasn't proposing to add it in 8.4. Just to add it to the TODO. Perhaps
someone would look at it some point in the future.

--

Med venlig hilsen
Kaare Rasmussen, Jasonic

Jasonic Telefon: +45 3816 2582
Nordre Fasanvej 12
2000 Frederiksberg Email: kaare@jasonic.dk

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On Wed, 2008-07-16 at 07:39 +0200, A. Kretschmer wrote:
> am Tue, dem 15.07.2008, um 18:15:07 -0700 mailte Mark Roberts
> folgendes:
> >
> > On Tue, 2008-07-15 at 14:31 +0200, A. Kretschmer wrote:
> > > am Tue, dem 15.07.2008, um 13:12:39 +0100 mailte Oliveiros
> Cristina folgendes:
> > > > Howdy, all,
> > > >
> > > > I have a problem.
> > > >
> > > > I have a table which one of the fields is of type date.
> > > >
> > > > I need to obtain the totals of the other fields in a by-month
> basis
> > > > IS there any easy way to do this using the GROUP BY or any other
> construct?
> > >
> > > ... group by extract(month from date)
> > >
> > >
> > > Andreas
> >
> > It's worth noting that extract(month from timestamp) returns a
> month_no, and thus will not be suitable for grouping queries that span
> years.
>
> Right, but that wasn't the question...

Honestly, the way the question was phrased, I'd have assumed that it
wanted to group by month (not group by a group of months). Jan 08 is
distinct from Jan 07.

Please accept my sincerest apologies if you you feel that I
misinterpreted the question. I was merely trying to illustrate the
difference between what each approach was.

-Mark


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

All,

Thanks a million for your help and thoughtful considerations.
From this thread I learned lots.
As my concrete problem just concerns one year, I used the extract function,
but I ve mentally wrote down the date_trunc construct as suggested by
Herouth

Andreas and Scott, thanks for the tips on indexing

Again, thanks a lot.

Best,
Oliveiros

----- Original Message -----
From: "Mark Roberts" <mailing_lists@pandapocket.com>
Cc: <pgsql-sql@postgresql.org>
Sent: Wednesday, July 16, 2008 5:29 PM
Subject: Re: [SQL] How to GROUP results BY month


>
> On Wed, 2008-07-16 at 07:39 +0200, A. Kretschmer wrote:
>> am Tue, dem 15.07.2008, um 18:15:07 -0700 mailte Mark Roberts
>> folgendes:
>> >
>> > On Tue, 2008-07-15 at 14:31 +0200, A. Kretschmer wrote:
>> > > am Tue, dem 15.07.2008, um 13:12:39 +0100 mailte Oliveiros
>> Cristina folgendes:
>> > > > Howdy, all,
>> > > >
>> > > > I have a problem.
>> > > >
>> > > > I have a table which one of the fields is of type date.
>> > > >
>> > > > I need to obtain the totals of the other fields in a by-month
>> basis
>> > > > IS there any easy way to do this using the GROUP BY or any other
>> construct?
>> > >
>> > > ... group by extract(month from date)
>> > >
>> > >
>> > > Andreas
>> >
>> > It's worth noting that extract(month from timestamp) returns a
>> month_no, and thus will not be suitable for grouping queries that span
>> years.
>>
>> Right, but that wasn't the question...
>
> Honestly, the way the question was phrased, I'd have assumed that it
> wanted to group by month (not group by a group of months). Jan 08 is
> distinct from Jan 07.
>
> Please accept my sincerest apologies if you you feel that I
> misinterpreted the question. I was merely trying to illustrate the
> difference between what each approach was.
>
> -Mark
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

i think this work

select id,count from table group by to_char(date,'MM')

--- On Wed, 7/16/08, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:

> From: A. Kretschmer <andreas.kretschmer@schollglas.com>
> Subject: Re: [SQL] How to GROUP results BY month
> To: pgsql-sql@postgresql.org
> Date: Wednesday, July 16, 2008, 5:39 AM
> am Tue, dem 15.07.2008, um 18:15:07 -0700 mailte Mark
> Roberts folgendes:
> >
> > On Tue, 2008-07-15 at 14:31 +0200, A. Kretschmer
> wrote:
> > > am Tue, dem 15.07.2008, um 13:12:39 +0100 mailte
> Oliveiros Cristina folgendes:
> > > > Howdy, all,
> > > >
> > > > I have a problem.
> > > >
> > > > I have a table which one of the fields is of
> type date.
> > > >
> > > > I need to obtain the totals of the other
> fields in a by-month basis
> > > > IS there any easy way to do this using the
> GROUP BY or any other construct?
> > >
> > > ... group by extract(month from date)
> > >
> > >
> > > Andreas
> >
> > It's worth noting that extract(month from
> timestamp) returns a month_no, and thus will not be
> suitable for grouping queries that span years.
>
> Right, but that wasn't the question...
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr:
> -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA
> http://wwwkeys.de.pgp.net
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql





--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Still another way to do :-)

Thanks, Lennin.

Best,
Oliveiros

----- Original Message -----
From: "Lennin Caro" <lennin.caro@yahoo.com>
To: <pgsql-sql@postgresql.org>; "A. Kretschmer"
<andreas.kretschmer@schollglas.com>
Sent: Friday, July 18, 2008 3:04 PM
Subject: Re: [SQL] How to GROUP results BY month


>
> i think this work
>
> select id,count from table group by to_char(date,'MM')
>
> --- On Wed, 7/16/08, A. Kretschmer <andreas.kretschmer@schollglas.com>
> wrote:
>
>> From: A. Kretschmer <andreas.kretschmer@schollglas.com>
>> Subject: Re: [SQL] How to GROUP results BY month
>> To: pgsql-sql@postgresql.org
>> Date: Wednesday, July 16, 2008, 5:39 AM
>> am Tue, dem 15.07.2008, um 18:15:07 -0700 mailte Mark
>> Roberts folgendes:
>> >
>> > On Tue, 2008-07-15 at 14:31 +0200, A. Kretschmer
>> wrote:
>> > > am Tue, dem 15.07.2008, um 13:12:39 +0100 mailte
>> Oliveiros Cristina folgendes:
>> > > > Howdy, all,
>> > > >
>> > > > I have a problem.
>> > > >
>> > > > I have a table which one of the fields is of
>> type date.
>> > > >
>> > > > I need to obtain the totals of the other
>> fields in a by-month basis
>> > > > IS there any easy way to do this using the
>> GROUP BY or any other construct?
>> > >
>> > > ... group by extract(month from date)
>> > >
>> > >
>> > > Andreas
>> >
>> > It's worth noting that extract(month from
>> timestamp) returns a month_no, and thus will not be
>> suitable for grouping queries that span years.
>>
>> Right, but that wasn't the question...
>>
>>
>> Andreas
>> --
>> Andreas Kretschmer
>> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr:
>> -> Header)
>> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA
>> http://wwwkeys.de.pgp.net
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql