-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Nov 8, 2012, at 6:47 AM, Audrius Kažukauskas wrote:
> 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.
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-----
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
