使用 SQLAlchemy Integer 字段创建用于过滤的 timedelta 对象

2021-12-30 00:00:00 python sqlalchemy timedelta mysql

我有一个名为 Item 的对象.它有 2 个字段:一个名为created_on"的日期时间行和一个名为days"的整数行.

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.

我认为应该这样做:

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

更新:

感谢 van，我应该使用内置函数.我使用的是 Mysql 5.1，所以它是 timestampadd.我的新查询是这样的:

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

推荐答案

我不认为 Interval 会在那里帮助你，因为它不能强制进入特定于数据库的日期[时间] 职能.所以你可能应该使用特定于平台的 date[time] 函数来解决这个问题.

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.

如果您正在使用 postgresql，下面的代码应该可以工作(假设 Items.days 是整数):

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

参见 postgres' 日期/Time 函数和运算符了解更多信息.

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

相关文章