Re: problem with timezone parsing

On Sun, Feb 15, 2009 at 10:13 PM, Karsten Hilbert
<[email protected]> wrote:
> PostgreSQL (on Debian/Lenny) comes with a bunch of time
> zones which psycopg2 cannot parse, such as Asia/Calcutta.
> Attempts to read a "timestamp with time zone" column with
> client timezone set to one of those zones throw an "unable
> to parse time" exception. Strangely enough, Asia/Colombo,
> which has the same utc offset (05:30:00) parses just fine.
>
> The corresponding time zones can be found with the attached
> script (make sure to adjust the DSN).
>
> That script also reports another category of time zones
> which PostgreSQL includes but cannot use ;-)   But that's
> beyond the scope of psycopg2.

By any chance are the problem dates fairly old?  Python's datetime
module can not represent time zones whose UTC offset is not an integer
number of minutes, which is a problem for some older dates in various
time zones.  For example, the Asia/Calcutta time zone lists:

Zone    Asia/Calcutta   5:53:28 -       LMT     1880    # Kolkata
                        5:53:20 -       HMT     1941 Oct    # Howrah Mean Time?
                        6:30    -       BURT    1942 May 15 # Burma Time
                        5:30    -       IST     1942 Sep
                        5:30    1:00    IST     1945 Oct 15
                        5:30    -       IST

Which is going to be a problem for dates up until 1941.  It isn't
clear how best to handle this: ignoring the seconds portion of the
offset would give you a value, but it would be the wrong value.

For projects I've worked on, we've generally used "timestamp without
time zone" and then done all the time zone handling application side
(the pytz library is quite helpful here).

James.