From: Richard Huxton <dev@archonet.com>
To: Wes Devauld <wes@devauld.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Aggregates (last/first) not behaving
Date: Thu, 22 Jul 2010 09:44:56 +0100
Message-ID: <4C480508.8080306@archonet.com> (raw)
In-Reply-To: <AANLkTim1g8cejgRokrpTr6a_z6bXeKxOhUSFeTIwMrcK@mail.gmail.com>
References: <AANLkTim1g8cejgRokrpTr6a_z6bXeKxOhUSFeTIwMrcK@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, wes@devauld.ca
Subject: Re: Aggregates (last/first) not behaving
In-Reply-To: <4C480508.8080306@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox