Re: Using infinite values with DateTimeTZRange

On Sun, Aug 20, 2017 at 3:23 PM, Fennell, Felix W. <felnne(at)bas(dot)ac(dot)uk> wrote:
> Hi,
>
> Apologies if this isn’t the right place to ask questions like this but I am having difficulty storing ‘infinite’ dates using the DateTimeTZRange object.

No, it's totally fine, thank you for not having opened a bug in the
bug tracker! :D

> I have a Postgres database with a ‘tstzrange’ column (named validity) and I’d like to store a value in this similar to ‘[“2010-01-01 10:00:00 +01”, infinity]’.
>
> I’m using SQL Alchemy’s ORM to store data from Python with a model containing:
>
> ```
> from sqlalchemy.dialects.postgresql import TSTZRANGE
> ...
>
> class Principle(Base):
> __tablename__ = 'principles'
>
> id = Column(Integer, primary_key=True)
> validity = Column(TSTZRANGE())
> ```
>
> And then code to generate a timestamp range, using ‘datetime.max()’ to represent infinity as mentioned here [1]:
>
> ```
> from psycopg2.extras import DateTimeTZRange
> ...
>
> from_now = timezone("utc").localize(datetime.now())
> until_forever = timezone("utc").localize(datetime.max)
>
> validity = DateTimeTZRange(from_now, until_forever)
> ```
>
> I then added the code from [2] to translate the use of datetime.max into 'infinity' for use in Postgres, however when I
> tried to save this model I got this error instead:
>
> 'argument 1 must be datetime.date, not DateTimeTZRange'
>
> I naively tried changing this line:
>
> ```
> psycopg2.extensions.register_adapter(datetime.date, InfDateAdapter)
> ```
>
> to:
>
> ```
> psycopg2.extensions.register_adapter(DateTimeTZRange, InfDateAdapter)
> ```
>
> But that gave me the same error. I'm afraid I don't know Python or this library well enough to adapt the snippet from
> [2] into a form that will work with timestamp ranges - assuming that's what I need to do?
>
> Does anyone here from any advice for how to make this work? I did try googling, but I kept getting directed back to [2].
>
> Thanks,
> Felix.
>
> [1] http://initd.org/psycopg/docs/extras.html#range-data-types
> [2] http://initd.org/psycopg/docs/usage.html#infinite-dates-handling

You have to adapt what's in [2] to work for datetime objects instead
of dates. Note that python represents with the same class both tz
naive and aware objects: if your program needs to handle both you will
have to perform extra checks in the adapter to dispatch them to the
right postgres type.

class InfDateTimeTZAdapter:
min_utc = timezone('utc').localize(datetime.min)
max_utc = timezone('utc').localize(datetime.max)
def __init__(self, wrapped):
self.wrapped = wrapped
def getquoted(self):
if self.wrapped == self.max_utc:
return b"'infinity'::timestamptz"
elif self.wrapped == self.min_utc:
return b"'-infinity'::timestamptz"
else:
return psycopg2.extensions.DateFromPy(self.wrapped).getquoted()

psycopg2.extensions.register_adapter(datetime, InfDateTimeTZAdapter)

Once the dt adapter is fixed, the range adapter will use it automatically:

>>> print psycopg2.extensions.adapt(validity).getquoted()
tstzrange('2017-08-21T12:59:11.486205+00:00'::date,
'infinity'::datetimetz, '[)')

On the other way around it seems the adapter is already doing what you expect:

>>> cur.execute("select %s", [validity])
>>> r = cur.fetchone()[0]
>>> r.upper == until_forever
True

but make sure to use psycopg 2.7.2 at least because of bug
<https://github.com/psycopg/psycopg2/issues/536>.

Hope this helps.

-- Daniele