The following bug has been logged online:
Bug reference: 3586
Logged by: George Wright
Email address: george.wright@infimatic.com
PostgreSQL version: 8.1.5
Operating system: SUSE Linux 10.2
Description: Time zone problem in SQL query
Details:
I checked release notes up to 8.2.4 but only found references to new USA DST
rules and time zone database changes for Canada and Australia.
When supplying a timezone for dates, PostGreSQL appears to return incorrect
rows for a query. Table is called tz. Sample data is shown at the top.
Notice the number of rows returned when -06 is supplied, differs from when
-03 through -05 are supplied. I'm in EDT when running the query. Data is in
EST. It appears the first row is returned in error when supplying -03
throught -05, but I'm not sure.
badger=> select logid, starttime from tz;
logid | starttime
-------+------------------------
1 | 2007-01-22 23:00:00-06
2 | 2007-01-22 23:30:00-06
3 | 2007-01-23 00:00:00-06
4 | 2007-01-23 00:30:00-06
5 | 2007-01-23 01:00:00-06
(5 rows)
badger=> select logid, starttime from tz where starttime BETWEEN '2007-01-22
02:30:00-03' and '2007-01-23 03:00:00-03';
logid | starttime
-------+------------------------
1 | 2007-01-22 23:00:00-06 <- is this right?
2 | 2007-01-22 23:30:00-06
3 | 2007-01-23 00:00:00-06
(3 rows)
badger=> select logid, starttime from tz where starttime BETWEEN '2007-01-22
01:30:00-04' and '2007-01-23 02:00:00-04';
logid | starttime
-------+------------------------
1 | 2007-01-22 23:00:00-06 <- is this right?
2 | 2007-01-22 23:30:00-06
3 | 2007-01-23 00:00:00-06
(3 rows)
badger=> select logid, starttime from tz where starttime BETWEEN '2007-01-22
00:30:00-05' and '2007-01-23 01:00:00-05';
logid | starttime
-------+------------------------
1 | 2007-01-22 23:00:00-06 <- is this right?
2 | 2007-01-22 23:30:00-06
3 | 2007-01-23 00:00:00-06
(3 rows)
badger=> select logid, starttime from tz where starttime BETWEEN '2007-01-22
23:30:00-06' and '2007-01-23 00:00:00-06';
logid | starttime
-------+------------------------
2 | 2007-01-22 23:30:00-06
3 | 2007-01-23 00:00:00-06
(2 rows)