Using a SQLAlchemy Integer field to create a timedelta object for filtering

Question

I have an object called Item. It has 2 fields: a datetime row called "created_on," and an integer row called "days."

I want to query for all objects that were created "days" many days ago.

Here's how I thought it should be done:

now = utcnow()
session.query(Item).filter(Item.created_on + Interval(timedelta(days=Item.days)) <= now)

But I'm unable to create a timedelta like this. I'm getting this error:

TypeError: unsupported type for timedelta minutes component: InstrumentedAttribute

Update:

Thanks to van, I should be using a built-in function. I'm using Mysql 5.1, so it would be timestampadd. My new query is this:

now = utcnow()
session.query(Item).filter(func.timestampadd('DAY', Item.days, Item.created_on) <= now)

However, the new error I'm getting is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''DAY', items.days, items.created_on) <= '2015-07-12 ' at line 3

van · Accepted Answer · 2015-07-12 10:08:37Z

9

I do not think that Interval will help you there, as it would not be able to coerce into DB-specific date[time] functions. So you should probably use the platform specific date[time] functions to solve this.

If you are using postgresql, the code below should work (assuming Items.days is integer):

q = (session.query(Item)
     .filter(func.age(now, Item.created_on) <=
             func.make_interval(0, 0, 0, Item.days)
             )
     )

See postgres' Date/Time Functions and Operators for more information.

answered Jul 12, 2015 at 10:08

van

77.6k13 gold badges174 silver badges179 bronze badges

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

2 Comments

mpounsett Over a year ago

And what if Item.days is a float?

van Over a year ago

@mpounsett: secs param is double. So just convert your value to seconds and pass as secs argument.

Asaf · Accepted Answer · 2018-11-27 11:22:19Z

0

now = utcnow()
session.query(Item).filter(Item.created_on + func.make_interval(0, 0, 0, Item.days) <= now)

answered Nov 27, 2018 at 11:22

Asaf

651 silver badge6 bronze badges

1 Comment

DaFois Over a year ago

It's always better to add some explanation to the answers. In this way also future users can understand better what is your contribute

Collectives™ on Stack Overflow

Using a SQLAlchemy Integer field to create a timedelta object for filtering

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related