Re: DateRange with mx.Date - Mailing list psycopg

On Thu, Jun 25, 2015 at 12:08 PM, Tilman Koschnick <til@subnetz.org> wrote:
> Hi,
>
> is there a way to adapt extras.DateRange to use extensions.MXDATE as a
> typecaster and accept/return mx.Date values directly?
>
> From the documentation, I couldn't make out how to do this using
> register_range(). I do not want to create a new range type within the
> database, just change the python type returned.

Hello Til,

The Range casters use whatever typecaster is registered for its base
type so, if you register the mx.Date typecaster the range one should
just work. Testing it seems working:

In [2]: import psycopg2
In [3]: cnn = psycopg2.connect('port=54393')
In [4]: cur = cnn.cursor()
In [5]: cur.execute("select '[2015-01-01,2015-01-31)'::daterange")
In [6]: cur.fetchone()[0]
Out[6]: DateRange(datetime.date(2015, 1, 1), datetime.date(2015, 1, 31), '[)')

In [7]: psycopg2.extensions.register_type(psycopg2.extensions.MXDATE)
In [8]: cur.execute("select '[2015-01-01,2015-01-31)'::daterange")
In [9]: cur.fetchone()[0]
Out[9]: DateRange(<mx.DateTime.DateTime object for '2015-01-01
00:00:00.00' at 7fa5ec5a2cd8>, <mx.DateTime.DateTime object for
'2015-01-31 00:00:00.00' at 7fa5ec5b9fa8>, '[)')

with the noticeable exception that there is no type mx.Date it seems,
only DateTime (sorry but I can't remember much about mx objects: I
haven't used them for a long time):

In [22]: mx.DateTime.Date(2015,1,1)
Out[22]: <mx.DateTime.DateTime object for '2015-01-01 00:00:00.00' at
7fa5ec577bb8>

This confuses Postgres when trying to use the DateRange objects with
them because:

In [13]: cur.execute("select %s",
[psycopg2.extras.DateRange(mx.DateTime.Date(2015,1,1),
mx.DateTime.Date(2015,1,31), '[)')])
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-13-9836bc762135> in <module>()
----> 1 cur.execute("select %s",
[psycopg2.extras.DateRange(mx.DateTime.Date(2015,1,1),
mx.DateTime.Date(2015,1,31), '[)')])

ProgrammingError: function daterange(timestamp without time zone,
timestamp without time zone, unknown) does not exist
LINE 1: select daterange('2015-01-01T00:00:00.000000'::timestamp, '2...
               ^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.

but you can use the DateTimeRange instead:

In [20]: cur.execute("select %s",
[psycopg2.extras.DateTimeRange(mx.DateTime.Date(2015,1,1),
mx.DateTime.Date(2015,1,31), '[)')])
In [21]: cur.fetchone()[0]
Out[21]: DateTimeRange(datetime.datetime(2015, 1, 1, 0, 0),
datetime.datetime(2015, 1, 31, 0, 0), '[)')

So it seems it should mostly work out of the box. I don't know if the
Date/DateTime confusion can be solved but it shouldn't be hard to copy
the adapter (from the _range.py module) and kick it until it complies
to adapt mx.DateTime objects as they were dates.

-- Daniele