Re: problem with timezone parsing

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.

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

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 ?

> 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.

> 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.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346