[Zope] Zope/Postgres problem with change to CST

28 Oct 2002

      Hi everyone,

I've built a small database application with Zope, PostgreSQL and
ZPsycopgDA. Unfortunately, since we in the central time zone of the U.S.
have switched from Daylight Saving Time to Standard Time, my app is now
generating errors.

Here's a Z SQL method that illustrates the problem:

--snip--
SELECT event.event_id, event_name, event_type, start_time, is_away,
org_name, activity_name,
       address, city
  FROM event_participant, event, participant, location
  WHERE
    <dtml-sqltest expr="ZopeTime().earliestTime()"
column="event.start_time" op="gt" type="string"> AND
    <dtml-sqltest expr="ZopeTime().latestTime() + numdays"
column="event.start_time" op="lt" type="string"> AND
    <dtml-sqltest org_name column="participant.org_name" op="eq"
type="string"> AND
    event_participant.event_id = event.event_id AND
    event_participant.participant_id = participant.participant_id AND
    location.location_id = event.location_id
  ORDER BY start_time
--snip--

The following error is produced when this Z SQL method is called:

--snip--
Error, psycopg.ProgrammingError: ERROR: Bad timestamp external
representation '2002/11/01 23:59:59 US/Central' SELECT event.event_id,
event_name, event_type, start_time, is_away, org_name, activity_name,
address, city FROM event_participant, event, participant, location WHERE
event.start_time > '2002/10/27' AND event.start_time < '2002/11/01
23:59:59 US/Central' AND participant.org_name = 'Henry Sibley High
School' AND event_participant.event_id = event.event_id AND
event_participant.participant_id = participant.participant_id AND
location.location_id = event.location_id ORDER BY start_time 
--------------------------------------------------------------------------------
SQL used:

SELECT event.event_id, event_name, event_type, start_time, is_away,
org_name, activity_name,
       address, city
  FROM event_participant, event, participant, location
  WHERE
    event.start_time > '2002/10/27' AND
    event.start_time < '2002/11/01 23:59:59 US/Central' AND
    participant.org_name = 'Henry Sibley High School' AND
    event_participant.event_id = event.event_id AND
    event_participant.participant_id = participant.participant_id AND
    location.location_id = event.location_id
  ORDER BY start_time
--snip--

Anyone else seeing something like this? BTW, this is Zope 2.5.1, Python
2.1.3 on Debian Linux.

-Tim

-- 
Tim Wilson      |   Visit Sibley online:   | Check out:
Henry Sibley HS |  http://www.isd197.org   | http://www.zope.com
W. St. Paul, MN |                          | http://slashdot.org
wilson@visi.com |  <dtml-var pithy_quote>  | http://linux.com