Zope/Postgres problem with change to CST
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
Hi Tim. What did the SQL query look like before (ie CDT vs CST)? Was the TZ format any different? Troy Tim Wilson wrote:
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
On Sun, Oct 27, 2002 at 09:21:53PM -0600, Troy Farrell wrote:
Hi Tim. What did the SQL query look like before (ie CDT vs CST)? Was the TZ format any different?
Hi Troy, I can't be sure, but I think the SQL query is unchanged. Of course, there's no way to get back and confirm that now. -Tim
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--
-- 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
Sure there is. I'll test it now. Currently rendered as: 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/10/28 23:59:59 US/Central' AND participant.org_name = 'OHS' 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 Earlier this week it would have been: 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/21' AND event.start_time < '2002/10/22 23:59:59 GMT-5' AND participant.org_name = 'OHS' 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 Hmmmm. I see a difference. Do you? I would recommend the strftime() function. See if that helps. Troy Tim Wilson wrote:
On Sun, Oct 27, 2002 at 09:21:53PM -0600, Troy Farrell wrote:
Hi Tim. What did the SQL query look like before (ie CDT vs CST)? Was the TZ format any different?
Hi Troy,
I can't be sure, but I think the SQL query is unchanged. Of course, there's no way to get back and confirm that now.
-Tim
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--
participants (2)
-
Tim Wilson -
Troy Farrell