4

Given a table

class Order(db.Model):
  created_at = db.Column(db.DateTime)
  days = db.Column(db.Integer)

I need to compose a query with FROM statement like this: Order.created_at + Order.days < datetime.now(). The simplest way doesn't work, since the result of addition integer to datetime is double :) This conclusion I've made after practical experiment with MySQL.

After searching a little I've found out correct SQL statement for MySQL which solves described issue:

SELECT *
FROM orders o
WHERE o.created_at + INTERVAL o.days DAY < '2014-06-10 14:22:00';

And what I'd like to ask is how to code the query above for sqlalchemy filter? I've found one article how to use Intervals here on stackoverflow, but DATEADD is missing in MySQL and I have no ideas where I need to bind an Interval.

Sorry for my poor English, I hope I could explain my problem correct :)

UPD: Maybe the right thing to define days as Interval, but currently it's not an option.

Community
  • 1
  • 1
spkps
  • 53
  • 1
  • 6

3 Answers3

4

MySQL has a function called TIMESTAMPADD which seems to do what you want. Here's how to use it in a query.

session.query(Order).filter(func.timestampadd(text('DAY'), Order.days, Order.created_at) < datetime(2014, 6, 10, 14, 22))
Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
davidism
  • 121,510
  • 29
  • 395
  • 339
1
from sqlalchemy import select, func

query = (session.query(Orders).filter(
             func.DATEADD('day', Orders.days, Orders.created_at) == '2014-06-10 14:22:00'))
jackotonye
  • 3,537
  • 23
  • 31
0

Old question, but for anyone using PSQL / SQlAlchemy 1.0.10+ you can use the below:

from sqlalchemy.sql import cast
db = SQLAlchemy()  # Instantiated 

Order.query.filter(
    Order.created_at > cast('1 DAY', db.Interval) * Order.day
).all()
JellyBeans
  • 31
  • 3