Re: [psycopg] Using infinite values with DateTimeTZRange

On Sun, Aug 20, 2017 at 3:23 PM, Fennell, Felix W. <felnne@bas.ac.uk> wrote:
> Hi,
>
> Apologies if this isn’t the right place to ask questions like this but I am having difficulty storing ‘infinite’
datesusing 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