Re: problem with datetime prior to 1960

On 17/03/2008, Christian Ledermann <[email protected]> wrote:
> Python 2.5.1
> Postgres 8.3.0
>
> timezone = 'Africa/Nairobi' (it may not be reproduceable with other
> timezones)
>
> the row date_of_birth is implemented as timestamp with timezone.
>
> >>> import psycopg2
>  >>> psycopg2.__version__
> '2.0.6 (dec dt ext pq3)'
> >>> dbcon = psycopg2.connect('dbname=mydb')
> >>> dbcur = dbcon.cursor()
> >>> dbcur.execute("SELECT * FROM users WHERE user_id=1")
>  >>> dbcur.fetchall()
> [(1, None, 'Mr.', 'Jimmy', 'Buru', 'Arengah', 'Lagos', 'M',
> datetime.datetime(2041, 12, 18, 0, 0,
> tzinfo=<psycopg2.tz.FixedOffsetTimezone object at 0xb7a0b0cc>), 'KE', None,
> 'ID xyzz', None, None, 'A', 'memberofparliament')]
>  >>>
>
> PLSQL:
>
> bungeni=# update users set date_of_birth='1960-12-18' where user_id=1;
> UPDATE 1
> bungeni=# select * from users where user_id=1
> ;
>  user_id | login | titles | first_name | last_name | middle_name |     email
>      | gender |     date_of_birth      | birth_country | date_of_death |
> national_id | password | salt | active_p |        type
> ---------+-------+--------+------------+-----------+-------------+----------------+--------+------------------------+---------------+---------------+-------------+----------+------+----------+--------------------
>        1 |       | Mr.    | Jimmy      | Buru      | Arengah     |
> [email protected] | M      | 1960-12-18 00:00:00+03 | KE            |
>     | ID xyzz   |          |      | A        | memberofparliament
>  (1 row)
>
> PYTHON:
> >>> dbcur.execute("SELECT * FROM users where user_id=1")
> >>> print dbcur.fetchall()
> [(1, None, 'Mr.', 'Jimmy', 'Buru', 'Arengah', '[email protected]', 'M',
> datetime.datetime(1960, 12, 18, 0, 0,
> tzinfo=<psycopg2.tz.FixedOffsetTimezone object at 0xb7c9226c>), 'KE', None,
> 'ID xyzz', None, None, 'A', 'memberofparliament')]
>
> PSQL:
> bungeni=# update users set date_of_birth='1959-12-18' where user_id=1;
> UPDATE 1
> bungeni=# select * from users where user_id=1
> ;
>  user_id | login | titles | first_name | last_name | middle_name |     email
>      | gender |        date_of_birth         | birth_country | date_of_death
> | national_id | password | salt | active_p |        type
> ---------+-------+--------+------------+-----------+-------------+----------------+--------+------------------------------+---------------+---------------+-------------+----------+------+----------+--------------------
>         1 |       | Mr.    | Jimmy      | Buru      | Arengah     |
> [email protected] | M      | 1959-12-18 00:00:00+02:44:45 | KE            |
>           | ID xyzz   |          |      | A        | memberofparliament
>  (1 row)
>
> PYTHON:
> >>> dbcur.execute("SELECT * FROM users where user_id=1")
> >>> print dbcur.fetchall()
> [(1, None, 'Mr.', 'Jimmy', 'Buru', 'Arengah', '[email protected]', 'M',
> datetime.datetime(1959, 12, 18, 0, 0), 'KE', None, 'ID xyzz', None, None,
> 'A', 'memberofparliament')]
>  >>>
> Traceback (most recent call last):
>   File "bin/python", line 153, in <module>
>     code.interact(banner="", local=globals())
>   File "/home/undesa/devel//lib/python2.5/code.py", line
> 302, in interact
>      console.interact(banner)
>   File "/home/undesa/devel//lib/python2.5/code.py", line
> 239, in interact
>     more = self.push(line)
>   File "/home/undesa/devel//lib/python2.5/code.py", line
> 261, in push
>      more = self.runsource(source, self.filename)
>   File "/home/undesa/devel//lib/python2.5/code.py", line
> 76, in runsource
>     code = self.compile(source, filename, symbol)
>   File "/home/undesa/devel//lib/python2.5/codeop.py", line
> 168, in __call__
>      return _maybe_compile(self.compiler, source, filename, symbol)
>   File "/home/undesa/devel//lib/python2.5/codeop.py", line
> 70, in _maybe_compile
>     for line in source.split("\n"):
> psycopg2.DataError: unable to parse time
>
> Note that the fetchall() does not raise the error immediatly but rather the
> next statement (here an empty cr)
>
> any pointers?

There are two problems here:

1. There is a bug in psycopg's time parsing code (parsing of time
zones in particular), where it doesn't return NULL after detecting an
error while parsing the time zone.  This causes the error to present
itself at a later

2. Even with that error fixed, the data won't be parsed because it is
only set up to accept time zones that Python will accept.  As Python's
datetime module only handles time zone offsets that are a whole number
of minutes, the offset "+02:44:45" can't be represented.

I'll look at fixing the first, but I'm not sure what I can do about the second.

James.