我试图使用Python3在MySQL中获得两个时间戳之间的数据。没有错误被抛出,但是我没有得到数据。
表设置如下:
cur.execute('''
CREATE TABLE IF NOT EXISTS payroll (
id INT PRIMARY KEY AUTO_INCREMENT,
employee_id INT,
payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
payment VARCHAR(20)
)
''')
下面是查询数据库的函数:
def get_payroll_payments_by_date(start, end):
c = create_connection()
cur = c[0]
db = c[1]
cur = db.cursor()
cur.execute("""
select payment from payroll
where DATE(payment_date) between %s and %s
order by payment_date desc;
""", (start, end))
data = cur.fetchall()
return data
print(get_payroll_payments_by_date('08/01/2021', '08/01/2021'))
查询返回一个空列表,即使日期范围中有数据。我看过类似的帖子,但我没有尝试返回数据。当我唤醒BETWEEN约束时,我得到数据。
mysql。日期格式为yyyy-mm-dd。
如果你使用它,你不需要像下面这样转换文本
CREATE TABLE IF NOT EXISTS payroll ( id INT PRIMARY KEY AUTO_INCREMENT, employee_id INT, payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, payment VARCHAR(20) )
INSERT INTO payroll VALUEs (NULL,1,NOW(), '1')
select payment from payroll where payment_date between STR_TO_DATE('08/01/2021','%d/%m/%Y') and STR_TO_DATE('08/01/2022','%d/%m/%Y') order by payment_date desc;
| payment ||:------ || 1 |
db<此处小提琴>此处小提琴>