SQLAlchemy: adding Integer column to DateTime

Question

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.

Ilja Everilä · Accepted Answer · 2019-02-14 07:07:01Z

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

edited Feb 14, 2019 at 7:07

Ilja Everilä

53.4k9 gold badges138 silver badges142 bronze badges

answered Jun 10, 2014 at 16:30

davidism

128k31 gold badges417 silver badges348 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jackotonye · Accepted Answer · 2016-12-29 22:02:07Z

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

edited Dec 29, 2016 at 22:02

answered Dec 29, 2016 at 21:39

jackotonye

3,84326 silver badges31 bronze badges

Comments

JellyBeans · Accepted Answer · 2021-11-09 23:57:00Z

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

answered Nov 9, 2021 at 23:57

JellyBeans

413 bronze badges

Comments

snakecharmerb · Accepted Answer · 2025-03-27 18:25:05Z

0

The following works with Postgres and I hope it works across the board for all the different databases because I feel that it is a better way to achieve this than using database specific functions like DATEADD or TIMESTAMPADD:

session.query(Order)
    .filter(
        Order.days * timedelta(days=1) + Order.created_at
        < 
        datetime(2014, 6, 10, 14, 22)
    )

to clarify this is just multiplying the days column by a datetime.timedelta object which casts it to an Interval in the query.

edited Mar 27 at 18:25

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

answered Mar 25 at 10:53

Waghabond

12 bronze badges

Collectives™ on Stack Overflow

SQLAlchemy: adding Integer column to DateTime

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related