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

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;