Re: problem with timezone parsing

On Mon, Feb 16, 2009 at 1:16 AM, Karsten Hilbert
<[email protected]> wrote:
> On Sun, Feb 15, 2009 at 10:33:37PM +0900, James Henstridge 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?
>
> If "1920" is "fairly old", then, yes.

It is old in the sense that it is from before the time zone adopted a
discrete offset from GMT rather than using the exact local time.

>
> Examples are the dates of birth of some people living
> today. This is a patient database (www.gnumed.de).
>
>> 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?
>
> That would apply - the example falls between 1880 and 1941.
>
>> 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.
> Would it be possible to throw a better defined exception ?
>
> Something like:
>
>        psycopg2.ProblematicTimeZoneError('cannot handle date ... in time zone ...').
>
> which would then allow me to catch it and try to work around
> it somehow (say, allow users to configure "fallback"
> timezones and retry the query).
>
> If it is a matter of seconds *I* would also be fine with:
>
>        try:
>                dob = ...
>        except psycopg2.TimestampRoundedDueToTimezoneWithSeconds, exc:
>                _log.warning('timestamp was truncated, %s', exc)
>                dob = exc.rounded_timestamp

I'm not sure how useful such an exception would be.  The exception
will be raised by one of fetchone(), fetchmany() or fetchall().  In
this case, you won't get the returned tuples.  And with
fetchmany/fetchall, it is likely that you'll see many similar problems
from the single call.


> IOW, round but raise a specific exception with the rounded
> timestamp as an argument and let the calling code decide.
>
> Or better yet:
>
>        try:
>                dob = ...
>        except psycopg2.CannotParseTimestampDueToTimezoneWithSeconds, exc:
>                _log.warning('timestamp was truncated, %s', exc)
>                dob = my_special_parsing_for_such_timestamps(exc.timestamp_as_if_UTC, exc.problematic_timezone)
>
> IOW, report the situation with a very narrow exception but
> also provide the data to enable calling code to conveniently
> handle the situation itself. This would be my favourite.
>
> Sounds OK ?


Well, you can do something like this by registering your own typecast
function for the type.  That way it'd be your code performing the
conversion from string to datetime object.  Take a look at
examples/typecast.py from the psycopg2 source for an example of the
how this is done.


>> For projects I've worked on, we've generally used "timestamp without
>> time zone"
> Which, then, doesn't allow to safely use some of the
> date/time arithmetic of PostgreSQL. Thanks for the
> suggestion, however.

Which datetime arithmetic functions would cause problems with this
method?  By storing everything in the database as a UTC timestamp, you
can compare values, work with intervals, etc.  The application just
needs to convert to and from UTC when walking to the database
(something which is pretty easy to enforce using an ORM).


>> and then done all the time zone handling application side
>> (the pytz library is quite helpful here).
> I recently learned of that and, yes, it looks helpful.

Yeah.  It works off the same tzinfo database that glibc and PostgreSQL
use.  It rounds offsets to the closest minute to work with Python's
datetime module limitations, but this is not a big problem if you're
always using the pytz definition and always converting user input to
UTC early and output to local time late.

James.