如何用Python的fdb库从Firebird的TIMESTAMP字段获取指定日期范围记录

我正在开发一款基于Firebird数据库的Python软件，使用fdb库进行数据库操作。尝试获取指定日期范围内的所有记录，但多次尝试均触发fdb异常，数据库中日期字段的数据类型为TIMESTAMP。

尝试1 #

'SELECT * FROM table WHERE date BETWEEN "2023-07-14" AND "2023-05-08"'

异常信息：

('Error while preparing SQL statement:
- SQLCODE: -206
- Dynamic SQL Error
- SQL error code = -206
- Column unknown
- 2023-07-16
- At line 1, column 73', -206, 335544569)

尝试2 #

'SELECT * FROM table WHERE date BETWEEN "2023-07-14 09:00" AND "2023-05-08 17:00"'

本次触发的异常与尝试1完全一致。

尝试3 #

'SELECT * FROM table WHERE date BETWEEN %s AND %s"' % (datetime.datetime(2023, 7, 16, 9), datetime.datetime(2023, 5, 8, 17)) 

异常信息：

('Error while preparing SQL statement:
- SQLCODE: -104
- Dynamic SQL Error
- SQL error code = -104
- Token unknown - line 2, column 48
- 09', -104, 335544569)

补充测试 #

通过测试发现问题出在日期类型处理上：

• 可正常运行的查询：

'SELECT * FROM table WHERE date = CURRENT_TIMESTAMP'

• 无法运行的查询：

'SELECT * FROM table WHERE date = "2023-07-16 22:00:00.000"'

已搜索谷歌但未找到相关解决方案，尝试使用CAST()转换类型也无效。

解决方法 #

Firebird的TIMESTAMP类型对日期格式的输入有特定要求，以下是几种可行的解决方式：

方法1：使用单引号包裹日期字符串 #

Firebird仅识别单引号包裹的日期时间字符串，将之前的双引号替换为单引号即可，同时注意日期范围需左小右大（否则会返回空结果）：

'SELECT * FROM table WHERE date BETWEEN \'2023-05-08 17:00:00\' AND \'2023-07-16 09:00:00\''

方法2：参数化查询（推荐） #

fdb原生支持参数化查询，直接传入Python的datetime对象，无需手动格式化字符串，既避免格式错误，又能防止SQL注入：

import datetime
import fdb

# 建立数据库连接
conn = fdb.connect(dsn='localhost:/path/to/your/db.fdb', user='sysdba', password='masterkey')
cur = conn.cursor()

# 定义日期范围（注意顺序：起始日期 <= 结束日期）
start_dt = datetime.datetime(2023, 5, 8, 17, 0, 0)
end_dt = datetime.datetime(2023, 7, 16, 9, 0, 0)

# 执行参数化查询
query = 'SELECT * FROM table WHERE date BETWEEN ? AND ?'
cur.execute(query, (start_dt, end_dt))
results = cur.fetchall()

# 处理结果
for row in results:
    print(row)

# 关闭连接
cur.close()
conn.close()

方法3：CAST显式类型转换 #

如果必须使用字符串形式，可通过CAST函数将字符串显式转换为TIMESTAMP类型：

'SELECT * FROM table WHERE date BETWEEN CAST(\'2023-05-08 17:00:00\' AS TIMESTAMP) AND CAST(\'2023-07-16 09:00:00\' AS TIMESTAMP)'

内容的提问来源于stack exchange，提问作者Nikita Silaev