When upgrading from version 0.7.0 to 0.7.2, SQLObject used inside Zope becomes unable to access PostgreSQL timestamp columns.
If a column has type "timestamp" or "timestamp with time zone" or "timestamp without time zone", it will be represented as a DateTimeCol in SQLObject.
The traceback below is obtained when trying to acess data from a table having such a timestamp column :
Module Products.Outdoor.Classes, line 1311, in getEchantillonTemoignages
Module sqlobject.sresults, line 155, in __iter__
Module sqlobject.dbconnection, line 721, in next
Module sqlobject.main, line 918, in get
Module sqlobject.main, line 964, in _init
Module sqlobject.main, line 1155, in _SO_selectInit
Module sqlobject.col, line 955, in to_python
Invalid: expected an date/time string of the '%Y-%m-%d %H:%M:%S' format in the DateTimeCol 'dateTemoig
nage', got <type 'instance'> DateTime('2006/12/13 11:41:40 GMT+0') instead
The error is the same regardless of whether the schema is defined explicitly in Python, or not (i.e. fromDatabase=True in the sqlmeta class).
The exact same fragment of code will work flawlessly from a Python script, or event from Zope's debug shell. It seems that Zope's DateTime implementation automatically appends timezone information to the string representation of the timestamp value.
A partial workaround would be to change the type of the column to 'text', but then it would no longer possible to perform date calculations on the value, or to use strftime formatting.
The release notes of SQLObject 0.7.2 indicate that some of the changes were specifically intended to make SQLObject compatible with Zope, but unfortunately the result seems to be exactly opposite...
My setup :
- FreeBSD 6.1
- Python 2.4.3
- Zope 2.9.6
- SQLObject 0.7.2
- PostgreSQL 8.1.5
(all software installed from FreeBSD Ports)