When grilled further on (Wed, 11 Jan 2006 07:26:59 -0700), Robert Creager <Robert_Creager@xxxxxxxxxxxxxxxx> confessed: > > weather-# SELECT *, unmunge_time( time_group ) AS time, > weather-# EXTRACT( doy FROM unmunge_time( time_group ) ) > weather-# FROM minute."windspeed" > weather-# JOIN doy_agg ON( EXTRACT( doy FROM unmunge_time( time_group ) ) = doy ) > weather-# WHERE unmunge_time( time_group ) > ( now() - '24 hour'::interval ) > weather-# AND doy BETWEEN EXTRACT( doy FROM now() - '24 hour'::interval) > weather-# AND EXTRACT( doy FROM now() ) > weather-# ORDER BY time_group; The more I think about it, the more I believe PG is missing an opportunity. The query is adequately constrained without the BETWEEN clause. Why doesn't PG see that? I realize I'm a hack and by db organization shows that... The query is wrong as stated, as it won't work when the interval crosses a year boundary, but it's a stop gap for now. Cheers, Rob -- 07:58:30 up 4 days, 25 min, 9 users, load average: 2.13, 2.15, 2.22 Linux 2.6.12-12-2 #4 SMP Tue Jan 3 19:56:19 MST 2006
Attachment:
pgpXWgLtPvOCk.pgp
Description: PGP signature
