Обсуждение: [psycopg] Using infinite values with DateTimeTZRange
Hi,
Apologies if this isn’t the right place to ask questions like this but I am having difficulty storing ‘infinite’ dates
usingthe DateTimeTZRange object.
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-0110: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
________________________________
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.
________________________________
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
On Mon, Aug 21, 2017 at 1:14 PM, Daniele Varrazzo
<daniele.varrazzo@gmail.com> wrote:
[...]
> 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, '[)')
Sorry, this is obviously a bug in handling the not infinity dates :)
The else should be:
return psycopg2.extensions.TimestampFromPy(self.wrapped).getquoted()
with which the range adapter will return an adapted value like:
tstzrange('2017-08-21T13:23:49.222454+00:00'::timestamptz,
'infinity'::datetimetz, '[)')
-- Daniele
On 08/20/2017 10:23 AM, Fennell, Felix W. 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. > > 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]’. While it is not an error to use 'infinity' or '-infinity' in a tstzrange, since those are acceptable timestamptz values, that is not the normal way to represent a range which extends infinitely in one direction. The normal way to represent a range with an infinite upper or lower bound is to omit the bound, as in '["2010-01-01 10:00:00 +01",]'. Read the manual for more detail: https://www.postgresql.org/docs/current/static/rangetypes.html#RANGETYPES-INFINITE -- Jonathan Rogers Socialserve.com by Emphasys Software jrogers@emphasys-software.com
Вложения
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.
________________________________
> While it is not an error to use 'infinity' or '-infinity' in a > tstzrange, since those are acceptable timestamptz values, that is not > the normal way to represent a range which extends infinitely in one > direction. The normal way to represent a range with an infinite upper or > lower bound is to omit the bound, as in '["2010-01-01 10:00:00 +01",]'. Thanks for that - in my case I want to distinguish between cases where the end-date for a range is not known, and where itjust been considered yet. The latter case I'm representing as a date-range without an upper bound, so thought 'infinity'was the best choice for where the end-date had been considered, but is unknown. Hope that reasoning makes sense, and if there's a better way to represent those two states then I'd be open to changing that. -- Felix __________________________________ From: psycopg-owner@postgresql.org <psycopg-owner@postgresql.org> on behalf of Jonathan Rogers <jrogers@emphasys-software.com> Sent: 22 August 2017 16:19 To: psycopg@postgresql.org Subject: Re: [psycopg] Using infinite values with DateTimeTZRange On 08/20/2017 10:23 AM, Fennell, Felix W. 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. > > 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]’. While it is not an error to use 'infinity' or '-infinity' in a tstzrange, since those are acceptable timestamptz values, that is not the normal way to represent a range which extends infinitely in one direction. The normal way to represent a range with an infinite upper or lower bound is to omit the bound, as in '["2010-01-01 10:00:00 +01",]'. Read the manual for more detail: https://www.postgresql.org/docs/current/static/rangetypes.html#RANGETYPES-INFINITE -- Jonathan Rogers Socialserve.com by Emphasys Software jrogers@emphasys-software.com ________________________________ 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. ________________________________
On 08/23/2017 06:14 PM, Fennell, Felix W. wrote: >> While it is not an error to use 'infinity' or '-infinity' in a >> tstzrange, since those are acceptable timestamptz values, that is not >> the normal way to represent a range which extends infinitely in one >> direction. The normal way to represent a range with an infinite upper or >> lower bound is to omit the bound, as in '["2010-01-01 10:00:00 +01",]'. > > Thanks for that - in my case I want to distinguish between cases where the end-date for a range is not known, and whereit just been considered yet. The latter case I'm representing as a date-range without an upper bound, so thought 'infinity'was the best choice for where the end-date had been considered, but is unknown. > > Hope that reasoning makes sense, and if there's a better way to represent those two states then I'd be open to changingthat. Yes, it does make sense and I've dealt with exactly the same problem. In some cases, I've decided to use separate timestamptz columns in a table to represent the upper and lower bounds. I was still able to take advantage of the feature of the range type system I've found most useful: an EXCLUDE constraint using the tstzrange constructor in the constraint. I found this approach to be more flexible in one crucial way. In one table, I needed to store date ranges in which a range could start and end on the same day. That was impossible using a daterange, since range types with the same upper and lower bounds are empty and lose all other information. > > -- Felix -- Jonathan Rogers Socialserve.com by Emphasys Software jrogers@emphasys-software.com