right way to do ::interval queries with orm?

[Chris Withers]

Hi All,

What's the "right" way to do a query such as the following using the orm
layer?

select * from event where date < now() - '3 years'::interval

Related: is there any intergration-testing-ish way to get now() to
return a specific value in postgres, rather than the real now?

cheers,

Chris

--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk

[Audrius Kažukauskas]

On Thu, 2012-11-08 at 10:06:54 +0000, Chris Withers wrote:
> What's the "right" way to do a query such as the following using the
> orm layer?
>
> select * from event where date < now() - '3 years'::interval

Here's how I do it in one of my projects:

from sqlalchemy import func
from sqlalchemy.sql.expression import FunctionElement
from sqlalchemy.ext.compiler import compiles

class subtract_interval(FunctionElement):
type = Date()
name = 'subtract_interval'

@compiles(subtract_interval)
def compile_subtract_interval(element, compiler, **kwargs):
return '(%s::date - %s::interval)' % (
compiler.process(element.clauses.clauses[0]),
compiler.process(element.clauses.clauses[1]),
)

res = session.query(Foo).filter(
Foo.date < subtract_interval(func.now(), '3 years')
).all()

I have borrowed the technique from an example that appeared on this same
mailing list in the past.

--
Audrius Kažukauskas
http://neutrino.lt/

[Michael Bayer]

Sqlalchemy as well as psycopg2 understands datetime.timedelta(), the cast is generated for you. No custom SQL needed.

Sent from my iPhone

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>
>

[Michael Bayer]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

yeah, I've shown this recipe as a means to provide cross-platform date arithmetic, specifically between Postgresql and SQL Server. But if you are on pure Postgresql, you can just use datetime.timedelta():

from sqlalchemy import *
import datetime

m = MetaData()
t = Table('event', m, Column('date', DateTime))

s = t.select().where(t.c.date < func.now() - datetime.timedelta(days=3 * 365))

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
m.drop_all(e)
m.create_all(e)
e.execute(t.insert(),
{'date': datetime.datetime(2007, 12, 15)},
{'date': datetime.datetime(2008, 12, 15)},
{'date': datetime.datetime(2004, 12, 15)},
{'date': datetime.datetime(2009, 12, 15)},
{'date': datetime.datetime(2010, 12, 15)},
)

print e.execute(s).fetchall()

>
> --
> Audrius Kažukauskas
> http://neutrino.lt/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.12 (Darwin)
Comment: GPGTools - http://gpgtools.org

iQEcBAEBAgAGBQJQm8npAAoJEDMCOcHE2v7hPUAH/3+g4Mlr1z5KICD0CXWug1oP
AsTkk2y1laD9ETE2sQfu/n7oowZqfDCyYTtVvLpXei4rWCFS7ZPj7tkBw8Pjemh1
HVbDFl5VcjFzgBm+RL1PRR0v/tUkpAN1Qu+PJUlI/gt1qcN/NOeTHAMoz5NqERsq
FFaQhN+y71XOeYxawu10TJamWtUSacuqhJSteheBRsqQ75oN7EWDZOLuiPb2lHLH
hVJF4BvHWZGjgMqrcs5yC8xj5+2w3GAsAkQIiGEu5kxaAOVClZT9okv7f8LjMiyI
hygpOKEwKY23Yr+EiTR7Xmaud7kYzb05fvVyr6aYeVMS8WBQsGNy7Tv/bq0tGQ4=
=+swm
-----END PGP SIGNATURE-----

[Audrius Kažukauskas]

On Thu, 2012-11-08 at 10:04:09 -0500, Michael Bayer wrote:
> yeah, I've shown this recipe as a means to provide cross-platform date
> arithmetic, specifically between Postgresql and SQL Server. But if
> you are on pure Postgresql, you can just use datetime.timedelta():
>
> from sqlalchemy import *
> import datetime
>
> m = MetaData()
> t = Table('event', m, Column('date', DateTime))
>
> s = t.select().where(t.c.date < func.now() - datetime.timedelta(days=3 * 365))

This is not equivalent to Postgres '3 years'::interval, as timedelta
does not take leap years into account, while interval does. PostgreSQL
interval behaves similar to dateutil.relativedelta, which has a notion
of months and years.