problem with dates prior to 1960-1-1 with psycop2

[Former user]

In postgres i have a table with column date_of_birth defined as:

date_of_birth | timestamp with time zone |

when i enter a date prior to 1960 i get the error: <class 'psycopg2.DataError'>: unable to parse time

File "/home/undesa/devel/svn/bungeni-portal/eggs/tmpZCLHfD/SQLAlchemy-0.4.3-py2.5.egg/sqlalchemy/orm/query.py", line 913, in iterate_instances File "/home/undesa/devel/svn/bungeni-portal/eggs/tmpZCLHfD/SQLAlchemy-0.4.3-py2.5.egg/sqlalchemy/engine/base.py", line 1619, in fetchall File "/home/undesa/devel/svn/bungeni-portal/eggs/tmpZCLHfD/SQLAlchemy-0.4.3-py2.5.egg/sqlalchemy/engine/base.py", line 1614, in _fetchall_impl File "/home/undesa/devel/svn/bungeni-portal/develop-eggs/tmpGEe7By/psycopg2-2.0.6-py2.5-linux-i686.egg/psycopg2/tz.py", line 39, in init DataError: unable to parse time

I can save these dates from sql-alchemy, and when i access them in another client (say psql) i can verify that they are set correctly, but as soon as i try to access them i get the above error. same behaviour if i set a date prior to 1960 in psql and try to access them with sqlalchemy.

[Former user]

File "/home/undesa/devel/svn/bungeni-portal/eggs/tmpZCLHfD/SQLAlchemy-0.4.3-py2.5.egg/sqlalchemy/orm/query.py", line 154, in get File "/home/undesa/devel/svn/bungeni-portal/eggs/tmpZCLHfD/SQLAlchemy-0.4.3-py2.5.egg/sqlalchemy/orm/query.py", line 975, in _get File "/home/undesa/devel/svn/bungeni-portal/eggs/tmpZCLHfD/SQLAlchemy-0.4.3-py2.5.egg/sqlalchemy/orm/query.py", line 780, in all File "/home/undesa/devel/svn/bungeni-portal/eggs/tmpZCLHfD/SQLAlchemy-0.4.3-py2.5.egg/sqlalchemy/orm/query.py", line 913, in iterate_instances File "/home/undesa/devel/svn/bungeni-portal/eggs/tmpZCLHfD/SQLAlchemy-0.4.3-py2.5.egg/sqlalchemy/engine/base.py", line 1619, in fetchall DataError: unable to parse time

[Former user]

further information: my postgres server is in the timezone 'Africa/Nairobi' so if i set a time in psql i get:

update user_group_memberships set start_date='1955-1-1 00:00:00' where membership_id = 3445; UPDATE 1 bungeni=# select * from user_group_memberships where membership_id = 3445; membership_id | user_id | group_id | title | start_date | end_date | notes | substitution_p | active_p | replaced_id | substitution_type ---------------+---------+----------+-------------+------------------------------+----------+-------+----------------+----------+-------------+------------------- 3445 | 149 | 195 | Chairperson | 1955-01-01 00:00:00 +02:44:45 | | | t | f | | (1 row)

bungeni=# update user_group_memberships set start_date='1965-1-1 00:00:00 ' where membership_id = 3445; UPDATE 1 bungeni=# select * from user_group_memberships where membership_id = 3445; membership_id | user_id | group_id | title | start_date | end_date | notes | substitution_p | active_p | replaced_id | substitution_type ---------------+---------+----------+-------------+------------------------+----------+-------+----------------+----------+-------------+------------------- 3445 | 149 | 195 | Chairperson | 1965-01-01 00:00:00 +03 | | | t | f | | (1 row)

[Mike Bayer]

the error is originating from within Psycopg2 so perhaps this is a psycopg2 issue ? what might be helpful here, or for them, is to turn on statement logging in postgres and take a look at the conversation present. But I would suggest creating a non-sqlalchemy test case, verifying that the error occurs with raw psycopg2, and then reporting this with psycopg2's tracker at http://www.initd.org/tracker/psycopg/wiki/PsycopgTwo , or on their mailing list (since their site seems to be down).

[Former user]

Reproduced it with pure psycopg2 and reported to the psycopg2 mailinglist.