在Jupyter Notebook中用Python编写PostgreSQL查询筛选过去12个月数据的问题

问题根源分析 #

你之前的代码返回空数据集，核心问题是时间格式转换失败，导致CASE WHEN返回NULL，而NULL无法匹配任何BETWEEN范围条件：

• 你使用的格式串'YYYY-MM-DDXHH24:MI:SSX'中的X是时区偏移符（比如+08），如果event字段里的时间字符串不带时区信息，to_timestamp会转换失败，返回NULL。
• 用format拼接SQL的方式容易出现引号、格式串匹配错误，且存在SQL注入风险。

修复步骤 #

1. 先确认event字段的实际格式

先执行以下代码查看event字段的样本数据，确定正确的时间格式串：

cur = conn.cursor()
cur.execute("SELECT event FROM cars.car_daily_data LIMIT 5;")
print("event字段样本：", cur.fetchall())

比如常见格式对应关系：

• '2023-05-20 14:30:00' → 格式串'YYYY-MM-DD HH24:MI:SS'
• '2023-05-20T14:30:00+08' → 格式串'YYYY-MM-DDXHH24:MI:SSX'
• '202305201430' → 格式串'YYYYMMDDHH24MI'

2. 使用参数化查询+动态时间范围

替换成以下代码，既避免格式拼接错误，又能自动获取过去12个月的数据：

cur = conn.cursor()

# 替换下面的格式串为你实际的event格式
event_format = 'YYYY-MM-DD HH24:MI:SS'

# 用TRY_TO_TIMESTAMP（PostgreSQL 12+支持）处理无效时间，避免报错
postgreSQL_select_Query = """
SELECT car_id, "event", "position"
FROM cars.car_daily_data 
WHERE try_to_timestamp(event, %s) BETWEEN CURRENT_DATE - INTERVAL '12 months' AND CURRENT_DATE
"""

# 用参数传递代替format拼接，安全且不易出错
cur.execute(postgreSQL_select_Query, (event_format,))
mobile_records = cur.fetchall()

3. 兼容旧版PostgreSQL（无TRY_TO_TIMESTAMP）

如果你的PostgreSQL版本低于12，用CASE WHEN配合格式校验：

cur = conn.cursor()
event_format = 'YYYY-MM-DD HH24:MI:SS'

postgreSQL_select_Query = """
SELECT car_id, "event", "position"
FROM cars.car_daily_data 
WHERE 
  CASE 
    WHEN event ~ '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$' -- 根据实际格式调整正则匹配规则
    THEN to_timestamp(event, %s)
    ELSE NULL
  END BETWEEN CURRENT_DATE - INTERVAL '12 months' AND CURRENT_DATE
"""

cur.execute(postgreSQL_select_Query, (event_format,))
mobile_records = cur.fetchall()

补充说明 #

• 不要直接用字符串比较（比如event > '2022-02-01'），字符串排序逻辑和时间排序逻辑可能不一致，必须转成时间类型后再比较。
• 用CURRENT_DATE - INTERVAL '12 months'代替硬编码日期，能自动适配当前时间，无需手动修改范围。

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