CODEWITHSUNDEEP
pythonsqlalchemy
spkps
spkps

Reputation: 53

SQLAlchemy: adding Integer column to DateTime

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.

Upvotes: 4

Views: 3134

Answers (3)

JellyBeans
JellyBeans

Reputation: 31

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()

Upvotes: 0

davidism
davidism

Reputation: 127180

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))

Upvotes: 4

jackotonye
jackotonye

Reputation: 3853

from sqlalchemy import select, func

query = (session.query(Orders).filter(
             func.DATEADD('day', Orders.days, Orders.created_at) == '2014-06-10 14:22:00'))

Upvotes: 1

Related Questions