Forum OpenACS Q&A: Re: timezone_rules -- inconsistent data (or an inconsistent developer?)

Here's a query that should reveal 1/2 the problems...??
select t.tz_id, t.utc_start, t.utc_end, u.utc_start, u.utc_end
 from timezone_rules t, timezone_rules u
where t.tz_id=u.tz_id
  and t.utc_start < u.utc_start
  and t.isdst_p = 'f' and u.isdst_p = 't'
  and u.utc_start < t.utc_end and
  not exists (select z.tz_id
                from timezone_rules z
               where z.utc_start between t.utc_end and u.utc_start)
order by tz_id, t.utc_start, u.utc_start
limit 20;

 tz_id |       utc_start        |        utc_end         |       utc_start        |        utc_end         
-------+------------------------+------------------------+------------------------+------------------------
    15 | 2001-10-28 01:00:00-08 | 2002-03-31 12:59:59-08 | 2002-03-31 01:00:00-08 | 2002-10-27 12:59:59-08
    15 | 2002-10-27 01:00:00-08 | 2003-03-30 12:59:59-08 | 2003-03-30 01:00:00-08 | 2003-10-26 12:59:59-08
    15 | 2003-10-26 01:00:00-08 | 2004-03-28 12:59:59-08 | 2004-03-28 01:00:00-08 | 2004-10-31 12:59:59-08
    15 | 2004-10-31 01:00:00-08 | 2005-03-27 12:59:59-08 | 2005-03-27 01:00:00-08 | 2005-10-30 12:59:59-08
    15 | 2005-10-30 01:00:00-08 | 2006-03-26 12:59:59-08 | 2006-03-26 01:00:00-08 | 2006-10-29 12:59:59-08
    15 | 2006-10-29 01:00:00-08 | 2007-03-25 12:59:59-08 | 2007-03-25 01:00:00-08 | 2007-10-28 12:59:59-08
    52 | 2002-04-07 12:00:00-07 | 2002-09-01 12:59:59-07 | 2002-09-01 01:00:00-07 | 2003-04-05 11:59:59-08
    52 | 2003-04-06 12:00:00-07 | 2003-09-07 12:59:59-07 | 2003-09-07 01:00:00-07 | 2004-04-03 11:59:59-08
    52 | 2004-04-04 12:00:00-07 | 2004-09-05 12:59:59-07 | 2004-09-05 01:00:00-07 | 2005-04-02 11:59:59-08
    52 | 2005-04-03 12:00:00-07 | 2005-09-04 12:59:59-07 | 2005-09-04 01:00:00-07 | 2006-04-01 11:59:59-08
    52 | 2006-04-02 12:00:00-07 | 2006-09-03 12:59:59-07 | 2006-09-03 01:00:00-07 | 2007-03-31 11:59:59-08
    52 | 2007-04-01 12:00:00-07 | 2007-09-02 12:59:59-07 | 2007-09-02 01:00:00-07 | 2008-04-05 11:59:59-08
    91 | 2001-10-28 01:00:00-07 | 2002-03-31 12:59:59-08 | 2002-03-31 01:00:00-08 | 2002-10-27 12:59:59-08
    91 | 2002-10-27 01:00:00-07 | 2003-03-30 12:59:59-08 | 2003-03-30 01:00:00-08 | 2003-10-26 12:59:59-08
    91 | 2003-10-26 01:00:00-07 | 2004-03-28 12:59:59-08 | 2004-03-28 01:00:00-08 | 2004-10-31 12:59:59-08
    91 | 2004-10-31 01:00:00-07 | 2005-03-27 12:59:59-08 | 2005-03-27 01:00:00-08 | 2005-10-30 12:59:59-08
    91 | 2005-10-30 01:00:00-07 | 2006-03-26 12:59:59-08 | 2006-03-26 01:00:00-08 | 2006-10-29 12:59:59-08
    91 | 2006-10-29 01:00:00-07 | 2007-03-25 12:59:59-08 | 2007-03-25 01:00:00-08 | 2007-10-28 12:59:59-08
   154 | 2001-10-28 01:00:00-08 | 2002-03-31 12:59:59-08 | 2002-03-31 01:00:00-08 | 2002-10-27 12:59:59-08
   154 | 2002-10-27 01:00:00-08 | 2003-03-30 12:59:59-08 | 2003-03-30 01:00:00-08 | 2003-10-26 12:59:59-08
(20 rows)