Message-ID: <4C480508.8080306@archonet.com>
Date: Thu, 22 Jul 2010 09:44:56 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.9pre) Gecko/20100217 Lightning/1.0b1 Shredder/3.0.3pre
MIME-Version: 1.0
To: Wes Devauld <wes@devauld.ca>
CC: pgsql-sql@postgresql.org
Subject: Re: Aggregates (last/first) not behaving
References: <AANLkTim1g8cejgRokrpTr6a_z6bXeKxOhUSFeTIwMrcK@mail.gmail.com>
In-Reply-To: <AANLkTim1g8cejgRokrpTr6a_z6bXeKxOhUSFeTIwMrcK@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 22/07/10 07:37, Wes Devauld wrote:
> I have PostgreSQL 8.3.9 [PostgreSQL 8.3.9 on i386-apple-darwin10.3.0,
> compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc.
> build 5646) (dot 1)]
>
> and the custom first and last aggregates from:
> http://wiki.postgresql.org/wiki/First_(aggregate)
> http://wiki.postgresql.org/wiki/Last_(aggregate)
>
> I have a simple table, of two columns.  The first is a timestamp and is
> the primary key, the second is an integer.  I've loaded the table up
> with values, one for every minute, for a whole year.  Some SQL to
> recreate the table and the aggregates can be retrieved from:
>
> http://blog.devauld.ca/aggregate_test.zip (File is approximately 180KB)
>
> Now when I try to make use of the first and last aggregates, I get:
>
> # select first(t), last(t) from test group by extract(day from t);
>          first        |        last
> ---------------------+---------------------
>   2009-01-01 00:00:00 | 2009-01-01 17:02:00
>   2009-01-02 10:07:00 | 2009-01-02 10:06:00
>   2009-01-03 20:15:00 | 2009-01-03 20:14:00
[snip]
> For some reason the aggregates are not falling into the proper group.  I
> can't blame timezones as the results are all over the map, and
> first/last relationship is broken as in some cases 'last' is
> chronologically before 'first'

They all seem grouped properly (by day) to me. Unless I've missed something.

The first/last aggregates aren't ordered in any way. They are "first 
value I happened to find" and "last value I happened to find".

If you want the earliest/latest timestamp from each day, use min() and 
max().

-- 
   Richard Huxton
   Archonet Ltd