Re: Using infinite values with DateTimeTZRange

Hi Daniele,

> You have to adapt what's in [2] to work for datetime objects instead
of dates.

Thanks for that sample code, it worked perfectly :) Seeing the two side-by-side makes much more sense as to how that works.

-- Felix
________________________________________
From: Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>
Sent: 21 August 2017 13:14:22
To: Fennell, Felix W.
Cc: psycopg(at)postgresql(dot)org
Subject: Re: [psycopg] 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
________________________________
This message (and any attachments) is for the recipient only. NERC is subject to the Freedom of Information Act 2000 and the contents of this email and any reply you make may be disclosed by NERC unless it is exempt from release under the Act. Any material supplied to NERC may be stored in an electronic records management system.
________________________________