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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
