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

Collapse
1: timezone_rules -- inconsistent data (or an inconsistent developer?)
Posted by Jerry Asher on 10/20/03 10:50 AM 
# select * from timezone_rules tr where tr.tz_id = 370 and '2004-03-28 06:00'::timestamp between utc_start and utc_end;
 tz_id | abbrev |       utc_start        |        utc_end         |      local_start       |       local_end        | gmt_offset | isdst_p | gmt_offset_interval 
-------+--------+------------------------+------------------------+------------------------+------------------------+------------+---------+---------------------
   370 | GMT    | 2003-10-26 01:00:00-08 | 2004-03-28 12:59:59-08 | 2003-10-26 01:00:00-08 | 2004-03-28 12:59:59-08 | 0          | f       | 00:00
   370 | BST    | 2004-03-28 01:00:00-08 | 2004-10-31 12:59:59-08 | 2004-03-28 02:00:00-08 | 2004-10-31 01:59:59-08 | 3600       | t       | 01:00
(2 rows)
My understanding of the timezone rules is that given a timezone, and a date and time, there should be one AND only one timezone rule that matches that date and time.

Yet in the example above, it sure seems that the two timezone rules overlap. Spring ahead, fall suggests that GMT 12:59 should probably be a 00:59.

Can someone A) verify this (maybe I corrupted my tables) B) Provide a nice little query to fix up all the timezone rules, C), Explain where the timezone rules came from so maybe we can generate a new set of rules with different problems?

Collapse
2: Re: timezone_rules -- inconsistent data (or an inconsistent developer?) (response to 1)
Posted by Jerry Asher on 10/20/03 11:31 AM
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)
Collapse
3: Re: timezone_rules -- inconsistent data (or an inconsistent developer?) (response to 1)
Posted by Jerry Asher on 10/20/03 12:07 PM
That query is somewhat right and somewhat wrong.

Here's what appears to be a query that finds the correct data, followed by a query that finds the bogus data. 

# select t.tz_id as ttz, t.utc_start as tst, t.utc_end as te,  
      u.utc_start as ust, u.utc_end as ue,
      t.local_start as tlst, t.local_end as tle,
      u.local_start as uls, u.local_end as ule
 from timezone_rules t, timezone_rules u
where t.tz_id=u.tz_id
  and t.utc_start < u.utc_start
  and u.utc_start > t.utc_end
  and not exists (select z.tz_id
                from timezone_rules z
               where z.tz_id = t.tz_id and z.utc_start > t.utc_start and z.utc_start < u.utc_start)
order by ttz, t.utc_start, u.utc_start
limit 30;




# select t.tz_id as ttz, t.utc_start as tst, t.utc_end as te,  
      u.utc_start as ust, u.utc_end as ue,
      t.local_start as tlst, t.local_end as tle,
      u.local_start as uls, u.local_end as ule
 from timezone_rules t, timezone_rules u
where t.tz_id=u.tz_id
  and t.utc_start < u.utc_start
  and u.utc_start < t.utc_end
  and not exists (select z.tz_id
                from timezone_rules z
               where z.tz_id = t.tz_id and z.utc_start > t.utc_start and z.utc_start < u.utc_start)
order by ttz, t.utc_start, u.utc_start
limit 30;