Re: problem with timezone parsing

On Wed, Feb 18, 2009 at 09:29:47PM +0100, Karsten Hilbert wrote:

> > Note that in released versions of psycopg2, the DATETIME caster will
> > sometimes succeed but give an incorrect result (any negative UTC
> > offset that isn't a whole number of hours).  So you might be best off
> > doing the time zone parsing yourself if it is important to you.
> 
> Anyway, I'll put in a warning and hope for a new psycopg2
> release soon :-)

So there :-)

# ----------------------------------------------------------------------
# PostgreSQL -> Python
# ----------------------------------------------------------------------

# We need this because some places once used time "zones"
# with true local time, IOW having seconds in the UTC offset.
# The Python datetime zone code cannot handle that, however,
# which makes psycopg2 fail when loading timestamps with such
# time zones from the backend ...
# So we (almost silently) drop the seconds and try again.
def convert_ts_with_odd_tz(string_value, cursor):
	try:
		return dbapi.DATETIME(string_value, cursor)
	except (dbapi.DataError,), exc:
		_log.error('unable to parse [%s]', string_value)

		if exc.message != "unable to parse time":
			raise

		_log.debug('unable to parse as <timestamp with time zone>')

		if regex.match('(\+|-)\d\d:\d\d:\d\d', string_value[-9:]) is None:
			raise

		if regex.match('-\d\d:\d\d:\d\d', string_value[-9:]) is not None:
			if string_value[-5:-3] != '00':
				_log.debug('psycopg2 versions < 2.0.8 may misinterpret this time zone: [%s]', string_value[-9:])

		# parsing doesn't succeed even if seconds
		# are ":00" so truncate in any case
		_log.debug('time zone with seconds detected (true local time ?): %s', string_value[-9:])
		truncated_string_value = string_value[:-3]
		_log.warning('truncating to [%s] and trying again', truncated_string_value)
		_log.warning('value will be off by %s seconds', string_value[-2:])
		return dbapi.DATETIME(truncated_string_value, cursor)


TIMESTAMPTZ_OID = 1184		# taken from PostgreSQL headers
if TIMESTAMPTZ_OID not in dbapi.DATETIME.values:
	raise ImportError('TIMESTAMPTZ_OID <%s> not in psycopg2.DATETIME.values [%s]' % (TIMESTAMPTZ_OID, dbapi.DATETIME.values))

#DT_W_ODD_TZ = psycopg2.extensions.new_type(dbapi.DATETIME.values, 'DT_W_ODD_TZ', convert_ts_with_odd_tz)
DT_W_ODD_TZ = psycopg2.extensions.new_type((TIMESTAMPTZ_OID,), 'DT_W_ODD_TZ', convert_ts_with_odd_tz)
psycopg2.extensions.register_type(DT_W_ODD_TZ)


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