日期超过一周的SQLite返回记录 [英] SQLite return records where date is more than a week old

问题描述

我正在为图书库制作一个程序，在sqlite数据库中有一张图书表，其中包括该书的最后取出日期。

I am making a program for a book library and in the sqlite database there is a table for the books which includes the date that book was last taken out.

我需要一个查询来显示过期的书。到目前为止，我的代码如下：

I need a query to show overdue books. My code so far follows:

def findOverdueBooks(event):
    findRecords = c.execute("SELECT * FROM bookList WHERE takenOut < 'now' ,  '-1 week' " )
    for row in findRecords:
        print(row)

运行代码时出现此错误

line 31, in findOverdueBooks
findRecords = c.execute("SELECT * FROM bookList WHERE takenOut < 'now' ,  '-1 week' " )
sqlite3.OperationalError: near ",": syntax error

我不明白为什么逗号引起错误，因为它是文档。

I don't understand why the comma is causing an error as that is how it is shown in the documentation.

推荐答案

您的日期时间比较导致了此问题。相反，应该是

Your datetime comparison is causing the issue here. It rather should be

AND takenOut < datetime('now', '-7 day')

请参见 SQLite日期和时间函数以获取更多信息

应该是> 比较

AND takenOut > datetime('now', '-7 day')

如果您只与日期部分进行比较

You can as well try like below, if you are comparing with the date part only

WHERE DATE(takenOut) >= DATE('now', 'weekday 0', '-7 days')

这篇关于日期超过一周的SQLite返回记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！