Re: problem with timezone parsing

On Wed, Feb 18, 2009 at 1:51 AM, Karsten Hilbert
<[email protected]> wrote:
> 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 !

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.

James.