[Zope] Zope/Postgres problem with change to CST

[Troy Farrell]

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--
>>
>