Thread: BUG #3586: Time zone problem in SQL query

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)

"George Wright" <george.wright@infimatic.com> writes:
> 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?

Why not?  2007-01-22 02:30:00-03 equates to 2007-01-21 23:30:00-06,
so it's before that time, and 2007-01-23 03:00:00-03 equates to
2007-01-23 00:00:00-06, so it's after.

> badger=> select logid, starttime from tz where starttime BETWEEN '2007-01-22
> 00:30:00-05' and '2007-01-23 01:00:00-05';
> badger=> select logid, starttime from tz where starttime BETWEEN '2007-01-22
> 23:30:00-06' and '2007-01-23 00:00:00-06';

Notice you forgot to adjust the date in the first value here, so these
are not equivalent boundary times.

            regards, tom lane