Re: How to GROUP results BY month

Hi all....
This is a very basic question.....can we roll back data after we run a
query.
I know that a delete within a transaction can be rolled back. But how about
independent delete queries???
If i ran a delete statement and lost data...how do i recover. I know that
oracle has this provision of rollingback queries.
Iam surprised iam not able to find the same in postgres.

Sam

On Fri, 2008-07-11 at 11:43 -0400, samantha mahindrakar wrote:
> Hi all....
> This is a very basic question.....can we roll back data after we run a
> query.
> I know that a delete within a transaction can be rolled back. But how
> about independent delete queries???
> If i ran a delete statement and lost data...how do i recover. I know
> that oracle has this provision of rollingback queries.
> Iam surprised iam not able to find the same in postgres.
>
> Sam

Postgres certainly can roll back queries, table creations, and many
other actions. You can find more information about rollback here:
http://www.postgresql.org/docs/8.3/interactive/sql-rollback.html

Best of luck in your endeavor :)

-Mark

On Fri, Jul 11, 2008 at 9:43 AM, samantha mahindrakar
<sam(dot)mahindrakar(at)gmail(dot)com> wrote:
> Hi all....
> This is a very basic question.....can we roll back data after we run a
> query.
> I know that a delete within a transaction can be rolled back. But how about
> independent delete queries???
> If i ran a delete statement and lost data...how do i recover. I know that
> oracle has this provision of rollingback queries.
> Iam surprised iam not able to find the same in postgres.

If you were not in a query, then you cannot just roll back. This is
because each statement is an individual transaction and a delete query
"outside" a transaction is actually a begin;delete...;commit; in
nature.

Oracle only supports the rollback after commit if you have the right
module installed and activated. And it uses up a fair bit of disk
space to do it. TANSTAAFL.

IF you have PITR setup in postgresql then you can recover to a
previous point in time. Otherwise, you need to restore from backups.

On Fri, 2008-07-11 at 11:21 -0600, Scott Marlowe wrote:
> rollback after commit

Are you sure?

Personally I don't think its viable. If it really does that it will
would also need to rollback all transactions whose changes depend upon
the earlier transaction. It would also need to track transactions that
read data changed by an earlier transaction and then makes changes to
the database. It's got no way to track that without extensive and costly
additional infrastructure, since after transaction commit row locking
information can be cleaned up by read-only transactions accessing those
changed data blocks.

Flashback query allows reading data as it was at a certain point in the
past. We might one day provide that, but undoing individual transactions
isn't ever going to be feasible, without unknowable risk.

Not jumping on you, just think their marketing is ahead of the reality.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

In addition to allowing you to read old data, Flashback will allow you to rollback to a point in time, including returning a single table to a specific state. Flashback database is like PITR without the log files.

It started in 9i and improved dramatically in 10g. 11g has made additional improvements.

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/intro007.htm

http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmflash.htm

Lewis R Cunningham

An Expert's Guide to Oracle Technology
http://blogs.ittoolbox.com/oracle/guide/

Postgres Forums
http://postgres.enterprisedb.com/forum.do

--- On Fri, 7/11/08, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> From: Simon Riggs <simon(at)2ndquadrant(dot)com>
> Subject: Re: [SQL] Rollback in Postgres
> To: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
> Cc: "samantha mahindrakar" <sam(dot)mahindrakar(at)gmail(dot)com>, pgsql-sql(at)postgresql(dot)org
> Date: Friday, July 11, 2008, 2:58 PM
> On Fri, 2008-07-11 at 11:21 -0600, Scott Marlowe wrote:
> > rollback after commit
>
> Are you sure?
>
> Personally I don't think its viable. If it really does
> that it will
> would also need to rollback all transactions whose changes
> depend upon
> the earlier transaction. It would also need to track
> transactions that
> read data changed by an earlier transaction and then makes
> changes to
> the database. It's got no way to track that without
> extensive and costly
> additional infrastructure, since after transaction commit
> row locking
> information can be cleaned up by read-only transactions
> accessing those
> changed data blocks.
>
> Flashback query allows reading data as it was at a certain
> point in the
> past. We might one day provide that, but undoing individual
> transactions
> isn't ever going to be feasible, without unknowable
> risk.
>
> Not jumping on you, just think their marketing is ahead of
> the reality.
>
> --
> Simon Riggs www.2ndQuadrant.com
> PostgreSQL Training, Services and Support
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

On Fri, 2008-07-11 at 18:56 -0700, Lewis Cunningham wrote:

> In addition to allowing you to read old data, Flashback will allow you
> to rollback to a point in time, including returning a single table to
> a specific state. Flashback database is like PITR without the log
> files.

Like I said: you cannot rollback a single transaction after commit.

Please don't put links to copyrighted material on our lists.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Sat, Jul 12, 2008 at 8:56 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> Please don't put links to copyrighted material on our lists.

That's an odd thing to say, given that virtually every link on our
lists probably points to material copyrighted in some way.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

On Sat, 2008-07-12 at 09:40 +0100, Dave Page wrote:
> On Sat, Jul 12, 2008 at 8:56 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> >
> > Please don't put links to copyrighted material on our lists.
>
> That's an odd thing to say, given that virtually every link on our
> lists probably points to material copyrighted in some way.

Prudence is all I ask for. We don't need to provide additional
advertising for others, nor do we wish to embroil ourselves in
accusations over copyright violations.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Sat, Jul 12, 2008 at 3:20 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> On Sat, 2008-07-12 at 09:40 +0100, Dave Page wrote:
>> On Sat, Jul 12, 2008 at 8:56 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> >
>> > Please don't put links to copyrighted material on our lists.
>>
>> That's an odd thing to say, given that virtually every link on our
>> lists probably points to material copyrighted in some way.
>
> Prudence is all I ask for. We don't need to provide additional
> advertising for others, nor do we wish to embroil ourselves in
> accusations over copyright violations.

Neither do we need to bury our heads in the sand and not know what's
happening in the world at large around us. I appreciated the links to
the Oracle docs. I can't see how a link to oracle documentation that
is open and requires no login can be an issue of copyright violation.
I might see some issue of "poisoning the well" as regards patented
methods getting into pgsql, but that's about it. As for advertising,
I doubt there's anyone on this list that just decided to switch to
Oracle over just those links.

What I would appreciate as regards Oracle's flashback technology would
have been a link to a well written review showing the warts as well as
the beauty. I've found that Oracle stuff sounds good on paper, and
turns into a giant maintenance nightmare upon deployment. But that's
just what I've seen looking over Oracle DBA shoulders in the past.

>
> Please don't put links to copyrighted material on our
> lists.
>

Postgres docs are copyrighted. The oracle docs are free to access just like the postgres docs. What is the issue?

LewisC

--- On Sat, 7/12/08, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:

> What I would appreciate as regards Oracle's flashback
> technology would
> have been a link to a well written review showing the warts
> as well as
> the beauty. I've found that Oracle stuff sounds good
> on paper, and
> turns into a giant maintenance nightmare upon deployment.
> But that's
> just what I've seen looking over Oracle DBA shoulders
> in the past.

Oracle-base is a site I trust and use. Tim writes very good articles and this is one he did recently covering flashback in 11g. The example on flashback transaction is the best I've seen.

http://www.oracle-base.com/articles/11g/FlashbackAndLogminerEnhancements_11gR1.php

Lewis R Cunningham

An Expert's Guide to Oracle Technology
http://blogs.ittoolbox.com/oracle/guide/

Postgres Forums
http://postgres.enterprisedb.com/forum.do

I didnt no the thread would become a postgresVSoracle thing. I just lost
couple of thousand rows and could not retrieve them back, so i wanted to
know if postgres had some way to get it back. Iam just a few days
expereinced in postgres hence iam still discovering its features.
No intention of comparing the two technologies......just trying find a
solution and ended up comparing because i had worked in oracle before and
very well knewit provide a rollback option for queries.
I dont see anything wrong in knowing what features oracle has.

Peace
Sam

On 7/12/08, Lewis Cunningham <lewisc(at)rocketmail(dot)com> wrote:
>
> --- On Sat, 7/12/08, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>
> > What I would appreciate as regards Oracle's flashback
> > technology would
> > have been a link to a well written review showing the warts
> > as well as
> > the beauty. I've found that Oracle stuff sounds good
> > on paper, and
> > turns into a giant maintenance nightmare upon deployment.
> > But that's
> > just what I've seen looking over Oracle DBA shoulders
> > in the past.
>
> Oracle-base is a site I trust and use. Tim writes very good articles and
> this is one he did recently covering flashback in 11g. The example on
> flashback transaction is the best I've seen.
>
>
> http://www.oracle-base.com/articles/11g/FlashbackAndLogminerEnhancements_11gR1.php
>
>
> Lewis R Cunningham
>
> An Expert's Guide to Oracle Technology
> http://blogs.ittoolbox.com/oracle/guide/
>
> Postgres Forums
> http://postgres.enterprisedb.com/forum.do
>
>
>
>
>
>

On Mon, Jul 14, 2008 at 9:20 AM, samantha mahindrakar
<sam(dot)mahindrakar(at)gmail(dot)com> wrote:
> I didnt no the thread would become a postgresVSoracle thing. I just lost
> couple of thousand rows and could not retrieve them back, so i wanted to
> know if postgres had some way to get it back. Iam just a few days
> expereinced in postgres hence iam still discovering its features.
> No intention of comparing the two technologies......just trying find a
> solution and ended up comparing because i had worked in oracle before and
> very well knewit provide a rollback option for queries.
> I dont see anything wrong in knowing what features oracle has.

Much like the processes that make up postgresql, the discussion can
fork in any number of directions. :)

I just lost a months worth of stats data myself, so join the club. It
wasn't critical data, but it would have been nice to have kept
around...

> I just lost a months worth of stats data myself, so join the club. It
> wasn't critical data, but it would have been nice to have kept
> around...

I also think there could be a TODO item in it. If vacuum instead of removing
items, somehow stashed them away in a storage limited archive it would be
possible to do a SELECT...AS OF TIMESTAMP.

The idea is of course to be able to retrieve rows that really are deleted, but
are still on disk as non-vacuumed or vacuumed and not removed completely. And
it would also take a 2. stage vacuumer to keep the storage within its limits.

I don't say it's an important feature, but it would come in handy for people
who really really need it. And perhaps a developer wouldn't mind scratching
this itch some time in the future.

--

Med venlig hilsen
Kaare Rasmussen, Jasonic

Jasonic Telefon: +45 3816 2582
Nordre Fasanvej 12
2000 Frederiksberg Email: kaare(at)jasonic(dot)dk

On Mon, Jul 14, 2008 at 12:59 PM, Kaare Rasmussen <kaare(at)jasonic(dot)dk> wrote:
> I also think there could be a TODO item in it. If vacuum instead of removing
> items, somehow stashed them away in a storage limited archive it would be
> possible to do a SELECT...AS OF TIMESTAMP.

This sounds a lot like the functionality that a temporal data model
would give you. In this model you never delete tuples from your
database, your only insert and update tuples that are valid for
specific periods of time.

If you want to contribute development time, I would check out
postgresql's temporal db project on PGfoundry. This project is just
getting started and could benefit from a lot of development help.

--
Regards,
Richard Broersma Jr.

Visit the Los Angles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Kaare Rasmussen escribió:

> I don't say it's an important feature, but it would come in handy for people
> who really really need it. And perhaps a developer wouldn't mind scratching
> this itch some time in the future.

It would need to be enabled beforehand, and most people I've seen for
which "it would come in handy" wouldn't have enabled it. (FWIW this
feature used to exist in the Berkeley code, under the cool name "time
travel", and was removed a long time ago.)

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Mon, 2008-07-14 at 21:59 +0200, Kaare Rasmussen wrote:
> > I just lost a months worth of stats data myself, so join the club. It
> > wasn't critical data, but it would have been nice to have kept
> > around...
>
> I also think there could be a TODO item in it. If vacuum instead of removing
> items, somehow stashed them away in a storage limited archive it would be
> possible to do a SELECT...AS OF TIMESTAMP.
>
> The idea is of course to be able to retrieve rows that really are deleted, but
> are still on disk as non-vacuumed or vacuumed and not removed completely. And
> it would also take a 2. stage vacuumer to keep the storage within its limits.

I've got the design all worked out for this.

The "only" thing we need is a VACUUM that will remove unseen data from
within the middle of the sum-of-all-snapshots, if there is a gap. At the
moment we never remove rows beyond global xmin, but we could iff the
transactions at xmin promise never to update data. That should go on the
TODO list as a precursor. Some discussion required :-)

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

> which "it would come in handy" wouldn't have enabled it. (FWIW this
> feature used to exist in the Berkeley code, under the cool name "time
> travel", and was removed a long time ago.)

No, it didn't AFAIK. Timetravel kept all tuples in the database with all
indexes and constraints active at all time. That's not the case with the
flashback technology. You put aside some storage space that you don't need
for something else. When that space is spent, tuples start dropping off the
edge.

I've talked to people who was very much happy with this feature. Mostly DBA's
recovering from their own stupid mistakes of course :-)

But yes, it has to be enabled, and yes it has to have a performance cost
somehow, but people are requesting it, and somehow I don't think Oracle
developed the feature just for fun. If you plug into Postgres' vacuum it
would be rather cheap to make, I recon. I wouldn't worry about query speed as
I guess that the use cases for retrieving already deleted rows don't aren't
performance dependant.

--

Med venlig hilsen
Kaare Rasmussen, Jasonic

Jasonic Telefon: +45 3816 2582
Nordre Fasanvej 12
2000 Frederiksberg Email: kaare(at)jasonic(dot)dk

> This sounds a lot like the functionality that a temporal data model
> would give you. In this model you never delete tuples from your
> database, your only insert and update tuples that are valid for
> specific periods of time.

Isn't this exactly what Alvaro describes? The time travel feature that was
removed because it made Postgres too slow to use in production?

--

Med venlig hilsen
Kaare Rasmussen, Jasonic

Jasonic Telefon: +45 3816 2582
Nordre Fasanvej 12
2000 Frederiksberg Email: kaare(at)jasonic(dot)dk

On Mon, Jul 14, 2008 at 1:38 PM, Kaare Rasmussen <kaare(at)jasonic(dot)dk> wrote:
> Isn't this exactly what Alvaro describes? The time travel feature that was
> removed because it made Postgres too slow to use in production?

No, I imagine that time travel was built into the Postgresql
architecture and would work automatically with transaction ids and
tuple ids.

On the other hand, temporal tables/schemes are implemented by the data
modeller. Also the associated temporal operations on the data would be
handled by client DML designed to simulate temporal data operations.

--
Regards,
Richard Broersma Jr.

Visit the Los Angles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

--- On Mon, 7/14/08, Kaare Rasmussen <kaare(at)jasonic(dot)dk> wrote:

> But yes, it has to be enabled, and yes it has to have a
> performance cost
> somehow, but people are requesting it, and somehow I

AFAIK, It is built from undo so there is no ADDITIONAL overhead. It just saves the undo that is created anyway for any DML anyway. That undo is already on disk.

Lewis R Cunningham

An Expert's Guide to Oracle Technology
http://blogs.ittoolbox.com/oracle/guide/

Database Wisdom
http://databasewisdom.com

Lewis Cunningham wrote:
> --- On Mon, 7/14/08, Kaare Rasmussen <kaare(at)jasonic(dot)dk> wrote:
>
> > But yes, it has to be enabled, and yes it has to have a
> > performance cost
> > somehow, but people are requesting it, and somehow I
>
> AFAIK, It is built from undo so there is no ADDITIONAL overhead. It
> just saves the undo that is created anyway for any DML anyway. That
> undo is already on disk.

Which means it doesn't work for us, because we don't have UNDO (we only
have REDO).

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Mon, 2008-07-14 at 22:38 +0200, Kaare Rasmussen wrote:
> > This sounds a lot like the functionality that a temporal data model
> > would give you. In this model you never delete tuples from your
> > database, your only insert and update tuples that are valid for
> > specific periods of time.
>
> Isn't this exactly what Alvaro describes? The time travel feature that was
> removed because it made Postgres too slow to use in production?

Similar. Performance is the issue to be solved with row removal, yes.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Kaare Rasmussen <kaare(at)jasonic(dot)dk> writes:
> But yes, it has to be enabled, and yes it has to have a performance cost
> somehow, but people are requesting it, and somehow I don't think Oracle
> developed the feature just for fun.

No, they developed it for marketing.

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.

regards, tom lane

On Mon, Jul 14, 2008 at 9:18 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Kaare Rasmussen <kaare(at)jasonic(dot)dk> writes:
>> But yes, it has to be enabled, and yes it has to have a performance cost
>> somehow, but people are requesting it, and somehow I don't think Oracle
>> developed the feature just for fun.
>
> No, they developed it for marketing.

No, they developed it because it was needed. In addition to knowing
quite a bit about the design and implementation of this feature, I've
been a production Oracle DBA and can speak from experience.

In fact, one of the primary reasons for creating this feature was for
the very purpose of why the original poster needed it, human-induced
disasters/mistakes. While flashback does give you the ability to
perform temporal-related queries, it was designed to allow recovery of
individual database objects (or the entire database itself) to a
certain point in time, thereby giving DBAs the ability to undo changes
(intentional or otherwise).

> Keep in mind that Oracle has six thousand full-time developers and an
> already extremely mature database.

True.

> Stuff that they see fit to add is not necessarily going to be on our radar
> screen in the foreseeable future.

Agreed.

--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah(dot)harris(at)enterprisedb(dot)com
Edison, NJ 08837 | http://www.enterprisedb.com/

On Mon, 2008-07-14 at 22:54 -0400, Jonah H. Harris wrote:
> On Mon, Jul 14, 2008 at 9:18 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Kaare Rasmussen <kaare(at)jasonic(dot)dk> writes:
> >> But yes, it has to be enabled, and yes it has to have a performance cost
> >> somehow, but people are requesting it, and somehow I don't think Oracle
> >> developed the feature just for fun.
> >
> > No, they developed it for marketing.
>
> No, they developed it because it was needed.

I agree such improvements would be welcomed. I'm pretty sure they sat
around saying we can already do that some other way at first, until the
requests started to pile up.

> > Stuff that they see fit to add is not necessarily going to be on our radar
> > screen in the foreseeable future.

I'm not clear on why there should be an inherent delay. I think
PostgreSQL adoption is mostly held back by operational features, like
performance management, locking, backup.

But we're mainly constrained on people's time, i.e. money. And AFAICS
nothing like this is going to happen in this release.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

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?

Thanks in advance for your kind help

Best,
Oliveiros

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

Oliveiros Cristina 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?
Yes, use date_trunc( 'month', time_stamp ).

Example:

select count(*),date_trunc( 'month', time_stamp ) from rb group by
date_trunc( 'month', time_stamp );

Herouth

Oliveiros Cristina 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?
Yes, use date_trunc( 'month', time_stamp ).

Example:

select count(*),date_trunc( 'month', time_stamp ) from rb group by
date_trunc( 'month', time_stamp );

Herouth

On Tue, Jul 15, 2008 at 2:17 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> I agree such improvements would be welcomed. I'm pretty sure they sat
> around saying we can already do that some other way at first, until the
> requests started to pile up.

Agreed.

>
>> > Stuff that they see fit to add is not necessarily going to be on our radar
>> > screen in the foreseeable future.
>
> I'm not clear on why there should be an inherent delay. I think
> PostgreSQL adoption is mostly held back by operational features, like
> performance management, locking, backup.

I agree. From what I've seen, the lack of good incremental backup is
still a major problem regarding adoption of large-scale systems.
Similarly, upgrade-in-place makes using PG fairly prohibitive in
several cases, though I'm glad Zdenek et al. are working on that. I
still hear lots of questions regarding, "how do I tell what PG is
doing right now?", which is why we (EnterpriseDB) developed RITA (the
Runtime Instrumentation and Tracing Architecture). DTrace is great,
but it's not cross-platform, can't be queried from within the
database, and until now, didn't really give you enough information to
diagnose many user-related problems.

> But we're mainly constrained on people's time, i.e. money. And AFAICS
> nothing like this is going to happen in this release.

Agreed.

--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah(dot)harris(at)enterprisedb(dot)com
Edison, NJ 08837 | http://www.enterprisedb.com/

On Tue, Jul 15, 2008 at 6:12 AM, Oliveiros Cristina
<oliveiros(dot)cristina(at)marktest(dot)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.

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

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.

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(dot)cristina(at)marktest(dot)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

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

> 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(at)jasonic(dot)dk

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

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(at)pandapocket(dot)com>
Cc: <pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)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(dot)kretschmer(at)schollglas(dot)com> wrote:

> From: A. Kretschmer <andreas(dot)kretschmer(at)schollglas(dot)com>
> Subject: Re: [SQL] How to GROUP results BY month
> To: pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)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(dot)caro(at)yahoo(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>; "A. Kretschmer"
<andreas(dot)kretschmer(at)schollglas(dot)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(dot)kretschmer(at)schollglas(dot)com>
> wrote:
>
>> From: A. Kretschmer <andreas(dot)kretschmer(at)schollglas(dot)com>
>> Subject: Re: [SQL] How to GROUP results BY month
>> To: pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>