PostgreSQL: interval columns, values measured in months or years

[Sibylle Koczian]

Hello,

I'm using the interval data type in two PostgreSQL databases with
intervals ranging from some days to several years. No problems with
intervals measured in days, but with all the interval values which can't
be correctly converted to datetime.timedelta.

All the calculations involving intervals are done by functions inside
the database, so for any applications I could just use the string
representations (display "2 mons" in a textbox, write the same). If I
just use one of the Python drivers for PostgreSQL (psycopg2,
py-postgresql), no ORM, I can write casts into the SQL as needed. But
how can I do this using SQLAlchemy? I tried to declare the column in the
mapped table as String, but that doesn't help, the values still are
converted to 30 days for a month.

Thank you for hints,
Sibylle

[Michael Bayer]

In the case of using Postgresql, the type sqlalchemy.dialects.postgresql.INTERVAL takes over wherever you might have used a sqlalchemy.Interval type. In this case, psycopg2 is what’s doing whatever conversions are occurring here - if a result row type has the Postgres OID for an “INTERVAL”, psycopg2 jumps in and does the conversion to timedelta. This isn’t on the SQLAlchemy side. If psycopg2 is doing the wrong thing you might want to look over on their side for updates or bug reports.

So if what you’re getting at is using CAST, so that by the time the result value hits psycopg2 it’s a plain character, SQLAlchemy uses the cast() function to achieve that result.

There’s ways to have this cast() embedded into a custom type also, so that it’s automatic, if that helps. Depends on the specifics of how you’re trying to do this.

>
> Thank you for hints,
> Sibylle
>
>

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.

[Sibylle Koczian]

Am 21.12.2013 16:27, schrieb Michael Bayer:
> In the case of using Postgresql, the type
> sqlalchemy.dialects.postgresql.INTERVAL takes over wherever you might
> have used a sqlalchemy.Interval type. In this case, psycopg2 is

> what�s doing whatever conversions are occurring here - if a result
> row type has the Postgres OID for an �INTERVAL�, psycopg2 jumps in
> and does the conversion to timedelta. This isn�t on the SQLAlchemy

> side. If psycopg2 is doing the wrong thing you might want to look
> over on their side for updates or bug reports.
>

I don't really know if psycopg2 is doing the wrong thing. Py-postgresql
does the same conversion. This might be viewed as a problem with
datetime.timedelta which isn't suitable for intervals of several months.

> So if what you�re getting at is using CAST, so that by the time the
> result value hits psycopg2 it�s a plain character, SQLAlchemy uses

> the cast() function to achieve that result.
>

Looks good, I'll try it. I think my problem is too little knowledge of
the SQL expression language, I've used only the ORM with the declarative
extension. So this may take a while.

Thank you for steering me into the right direction,
Sibylle

[Michael Bayer]

On Dec 23, 2013, at 9:29 AM, Sibylle Koczian <nulla.e...@web.de> wrote:

> Am 21.12.2013 16:27, schrieb Michael Bayer:
>> In the case of using Postgresql, the type
>> sqlalchemy.dialects.postgresql.INTERVAL takes over wherever you might
>> have used a sqlalchemy.Interval type. In this case, psycopg2 is

>> what’s doing whatever conversions are occurring here - if a result
>> row type has the Postgres OID for an “INTERVAL”, psycopg2 jumps in
>> and does the conversion to timedelta. This isn’t on the SQLAlchemy

>> side. If psycopg2 is doing the wrong thing you might want to look
>> over on their side for updates or bug reports.
>>
> I don't really know if psycopg2 is doing the wrong thing. Py-postgresql does the same conversion. This might be viewed as a problem with datetime.timedelta which isn't suitable for intervals of several months.

maybe you want to check on that because I’m not familiar with any such limitation in datetime.timedelta, it essentially stores a number of days. Below is an example using timedeltas of twelve years, eight months, and four days:

>>> import datetime
>>> datetime.datetime(1992, 12, 19) - datetime.datetime(1980, 4, 15)
datetime.timedelta(4631)
>>> d1 = datetime.datetime(1992, 12, 19) - datetime.datetime(1980, 4, 15)
>>> datetime.datetime(1992, 12, 19) + d1
datetime.datetime(2005, 8, 24, 0, 0)

[Laurence Rowe]

Postgres' INTERVAL supports deltas of quantities other than days, +1 month may mean 28/29/30/31 days depending on the month. From http://stackoverflow.com/questions/546321/how-do-i-calculate-the-date-six-months-from-the-current-date-using-the-datetime, dateutil has support for a richer relativedelta type and this can configure it on the psycopg2 level: https://pypi.python.org/pypi/psycopg2-dateutils

Laurence

[Sibylle Koczian]

Am 24.12.2013 08:11, schrieb Laurence Rowe:
>
> Postgres' INTERVAL supports deltas of quantities other than days, +1
> month may mean 28/29/30/31 days depending on the month. From
> http://stackoverflow.com/questions/546321/how-do-i-calculate-the-date-six-months-from-the-current-date-using-the-datetime,

> dateutil has support for a richer relativedelta type andthis can

> configure it on the psycopg2 level:
> https://pypi.python.org/pypi/psycopg2-dateutils
>

Exactly, that's it. The stackoverflow discussion was very instructive,
thank you! I'll try out psycopg2-dateutils.

Greetings,
Sibylle

[David Bolen]

Sibylle Koczian <nulla.e...@web.de> writes:

> Exactly, that's it. The stackoverflow discussion was very instructive,
> thank you! I'll try out psycopg2-dateutils.

I'm a big fan of dateutil. If you do use it, you may also choose to
bypass the use of timedelta entirely, since as you've seen it can fail
to accurately represent the database value (timedelta only has days,
minutes and seconds as components).

For example, I use the code below to map PostgreSQL interval columns
directly to dateutil's relativedelta subclass.

Any returned queries involving interval values will use the new
Interval type automatically, and will accurately round-trip. You'll
have to use the Interval class explicitly when creating values to go
the other way.

The mapping occurs purely at the psycopg2 level.

-- David


#
# --------------------------------------------------------------------------
# Interval Mapping
#
# Override normal psycopg2 mapping so Interval types get mapped into a
# relativedelta since it can more accurately represent them. We use our own
# relativedelta subclass as we need it to be a new style class for psycopg2
# adaptation to work. Other code must use our Interval class if trying to
# store such data back into the database.
#
# In this case there's nothing to do at the SQLAlchemy layer because for
# PostgreSQL, it just uses the data as provided by psycopg2.
# --------------------------------------------------------------------------
#

class Interval(relativedelta, object):
pass

import psycopg2
from psycopg2._psycopg import INTERVAL as psycopg2_INTERVAL
from psycopg2.extensions import new_type, register_type, register_adapter, AsIs
import re

interval_re = re.compile(' *'
'((?P<years>\d+) y\D*)?'
'((?P<months>\d+) m\D*)?'
'((?P<days>\d+) d\D*)?'
'((?P<hours>\d+):(?P<minutes>\d+):(?P<seconds>\d+))?'
'(\.(?P<subseconds>\d+))?$')


# Configure handling for supplying an Interval to store in the database

def adapt_interval(interval):
adapter = AsIs("'%d years %d months %d days %02d:%02d:%02d.%06d'" %
(interval.years, interval.months, interval.days,
interval.hours, interval.minutes, interval.seconds,
interval.microseconds))
return adapter

register_adapter(Interval, adapt_interval)

# Configure handling upon receiving an Interval from the database

def cast_interval(value, cur):
if value is None:
return None

m = interval_re.match(value)
if m:
vals = m.groupdict(0)
# Most everything is direct, but subseconds has a varying precision,
# so force it to be microseconds if we had a value
microseconds = vals['subseconds']
if microseconds:
microseconds = (microseconds + '000000')[:6]
return Interval(years=int(vals['years']),
months=int(vals['months']),
days=int(vals['days']),
hours=int(vals['hours']),
minutes=int(vals['minutes']),
seconds=int(vals['seconds']),
microseconds=int(microseconds))

else:
raise InterfaceError("Bad interval representation: %r" % value)

INTERVAL = new_type(psycopg2_INTERVAL.values, "INTERVAL", cast_interval)
register_type(INTERVAL)

[Wichert Akkerman]

On 25 Dec 2013, at 00:27, David Bolen <db3l...@gmail.com> wrote:

> Sibylle Koczian <nulla.e...@web.de> writes:
>
>> Exactly, that's it. The stackoverflow discussion was very instructive,
>> thank you! I'll try out psycopg2-dateutils.
>
> I'm a big fan of dateutil. If you do use it, you may also choose to
> bypass the use of timedelta entirely, since as you've seen it can fail
> to accurately represent the database value (timedelta only has days,
> minutes and seconds as components).
>
> For example, I use the code below to map PostgreSQL interval columns
> directly to dateutil's relativedelta subclass.

How does your code differ from https://pypi.python.org/pypi/psycopg2-dateutils ?

Wichert.

[Wichert Akkerman]

On 24 Dec 2013, at 08:11, Laurence Rowe <lauren...@gmail.com> wrote:

Postgres' INTERVAL supports deltas of quantities other than days, +1 month may mean 28/29/30/31 days depending on the month. From http://stackoverflow.com/questions/546321/how-do-i-calculate-the-date-six-months-from-the-current-date-using-the-datetime, dateutil has support for a richer relativedelta type and this can configure it on the psycopg2 level: https://pypi.python.org/pypi/psycopg2-dateutils

In theory that works. Unfortunately the psycopg2-dateutils is two years old and still not installable due to a bug in its setup.py.

Wichert.

[David Bolen]

Wichert Akkerman <wic...@wiggy.net> writes:

> How does your code differ from https://pypi.python.org/pypi/psycopg2-dateutils ?

Ah, I skipped that second URL earlier and only looked through the SO
discussion...

I guess two major differences I see are that psycopg2-dateutils
appears to only handle the database->python direction, but not the
reverse, but that it also translates arrays of intervals.

More importantly, it handles negative intervals properly which I don't
currently do (wow, ~3.5 years without needing that). So it wins for
accuracy.

-- David

[David Feinzeig]

Hi,

I think I've encountered my first time caring about psycopg2 mangling intervals and this looks super helpful. I was wondering if you have any updated suggestions since this post is a few years old?

Thanks!

David

On Tuesday, December 24, 2013 at 6:27:06 PM UTC-5, David Bolen wrote: