Skip site navigation (1) Skip section navigation (2)

Re: How to GROUP results BY month

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: "Mark Roberts" <mailing_lists(at)pandapocket(dot)com>
Cc: pgsql-sql <pgsql-sql(at)postgresql(dot)org>
Subject: Re: How to GROUP results BY month
Date: 2008-07-16 01:55:28
Message-ID: dcc563d10807151855o2ac94943n4c4ed7a75123d73a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
On Tue, Jul 15, 2008 at 7:15 PM, Mark Roberts
<mailing_lists(at)pandapocket(dot)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.

In response to

pgsql-sql by date

Next:From: Decibel!Date: 2008-07-16 03:13:42
Subject: Re: COPY equivalent for updates
Previous:From: Mark RobertsDate: 2008-07-16 01:15:07
Subject: Re: How to GROUP results BY month

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group