Re: [psycopg] Using infinite values with DateTimeTZRange - Mailing list psycopg
From | Fennell, Felix W. |
---|---|
Subject | Re: [psycopg] Using infinite values with DateTimeTZRange |
Date | |
Msg-id | AM3PR06MB1442B2360D3B021C14BA99049C850@AM3PR06MB1442.eurprd06.prod.outlook.com Whole thread Raw |
In response to | Re: [psycopg] Using infinite values with DateTimeTZRange (Daniele Varrazzo <daniele.varrazzo@gmail.com>) |
List | psycopg |
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.varrazzo@gmail.com> Sent: 21 August 2017 13:14:22 To: Fennell, Felix W. Cc: psycopg@postgresql.org Subject: 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 ________________________________ This message (and any attachments) is for the recipient only. NERC is subject to the Freedom of Information Act 2000 andthe contents of this email and any reply you make may be disclosed by NERC unless it is exempt from release under theAct. Any material supplied to NERC may be stored in an electronic records management system. ________________________________