Re: problem with timezone parsing

On Tue, Feb 17, 2009 at 05:00:53PM +0100, Federico Di Gregorio wrote:

> > Is there a way to install a module wide type caster ?
...
> Yes, just don't pass that parameter or pass None.
...
> > Is there a way to not need to access the DB (for learning
> > the type OID) and still use new_type (which seems a
> > prerequisite for register_type).
> 
> If you know the oid you can hard-code it into your code.
...
> > Thereby the second question can be answered yes but needs
> > reconfirmation that re-using those OIDs is reasonably safe ?
> 
> It is completely safe. They are generated from your PostgreSQL headers
> and guaranteed to be stable.

Thanks for all the help and comments. This is what I came up
with. It works for me:



# ----------------------------------------------------------------------
# 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
def convert_ts_with_odd_tz(string_value, cursor):
	try:
		return dbapi.DATETIME(string_value, cursor)
	except dbapi.DataError:
		_log.error('unable to parse [%s] as <timestamp with time zone>', string_value)
		if regex.match('(\+|-)\d\d:\d\d:\d\d', string_value[-9:]) is not None:
			# parsing doesn't succeed even if seconds
			# are ":00" so truncate in any case
			_log.debug('time zone with seconds detected (true local time ?)')
			adjusted_string_value = string_value[:-3]
			_log.warning('truncating to [%s] and trying again', adjusted_string_value)
			_log.warning('value will be off by %s seconds', string_value[-2:])
			return dbapi.DATETIME(adjusted_string_value, cursor)
		raise

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



Hope that helps someone down the line !

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