SqlAlchemy+MySQL日期运算筛选会话失败问题排查

问题背景 #

现有session表结构如下：

+---------------+----------+------+-----+---------+----------------+
| Field         | Type     | Null | Key | Default | Extra          |
+---------------+----------+------+-----+---------+----------------+
| session_start | datetime | YES  |     | NULL    |                |
| session_end   | datetime | YES  |     | NULL    |                |
+---------------+----------+------+-----+---------+----------------+

需求逻辑：当传入的hit记录的remote_stamp（Python datetime对象）满足 不早于session_start减去session_timeout秒，且不晚于session_end加上session_timeout秒 时，该hit归属对应会话。

当前使用SQLAlchemy编写的查询始终返回空结果，即使remote_stamp符合现有会话的±3600秒条件，也会进入创建新会话的分支：

matchingSession = models.SessionList.query.filter(and_(
    models.SessionList.session_start - timedelta(0,session_timeout) <= hit.remote_stamp,
    models.SessionList.session_end + timedelta(0,session_timeout) >= hit.remote_stamp
)).first() 

if matchingSession:
 print("Updating existing session.")
else:
 print("Session not found. Creating new session.")

补充hits表样本数据：

MariaDB [cbts]> select * from hits limit 10;
+-----+---------------------+---------------------+------+---------+------+----------+
| id  | stamp               | remote_stamp        | hits | quality | accu | is_valid |
+-----+---------------------+---------------------+------+---------+------+----------+
| 126 | 2021-12-27 13:41:11 | 2021-12-27 13:41:10 |   16 |      37 |    2 |        0 |
| 127 | 2021-12-27 13:41:17 | 2021-12-27 13:41:17 |   16 |      41 |    2 |        0 |
| 128 | 2021-12-27 13:42:02 | 2021-12-27 13:42:01 |   16 |      41 |    2 |        1 |
| 129 | 2021-12-27 13:43:03 | 2021-12-27 13:43:03 |   17 |      39 |    2 |        1 |
| 130 | 2021-12-27 13:43:15 | 2021-12-27 13:43:15 |   18 |      43 |    2 |        0 |
| 131 | 2021-12-27 13:47:31 | 2021-12-27 13:47:31 |   18 |      50 |    2 |        0 |
| 132 | 2021-12-27 13:49:19 | 2021-12-27 13:49:19 |   23 |      51 |    2 |        0 |
| 133 | 2021-12-27 13:49:44 | 2021-12-27 13:49:44 |   13 |      32 |    2 |        1 |
| 134 | 2021-12-28 15:25:37 | 2021-12-28 15:25:36 |    4 |      40 |    2 |        0 |
| 135 | 2021-12-28 21:58:44 | 2021-12-28 21:58:43 |   34 |      30 |    2 |        0 |
+-----+---------------------+---------------------+------+---------+------+----------+

预期逻辑：匹配到对应会话则更新（如增加点击量），否则创建新会话。

排查与修复方案 #

1. 修正SQLAlchemy时间运算逻辑 #

直接使用Python的timedelta与ORM字段进行运算，无法正确转换为数据库层面的时间操作，导致查询条件失效。可采用两种可行写法：

写法一：使用SQLAlchemy函数实现数据库端时间计算

from sqlalchemy import func, and_

matchingSession = models.SessionList.query.filter(and_(
    # remote_stamp >= session_start - session_timeout 等价于 session_start <= remote_stamp + session_timeout
    models.SessionList.session_start <= func.date_add(hit.remote_stamp, func.interval(session_timeout, 'second')),
    # remote_stamp <= session_end + session_timeout 等价于 session_end >= remote_stamp - session_timeout
    models.SessionList.session_end >= func.date_sub(hit.remote_stamp, func.interval(session_timeout, 'second'))
)).first()

写法二：先计算时间范围，再匹配会话区间

提前在Python端算出remote_stamp的允许范围，再判断会话的时间区间是否与该范围重叠：

from datetime import timedelta
from sqlalchemy import and_

lower_bound = hit.remote_stamp - timedelta(seconds=session_timeout)
upper_bound = hit.remote_stamp + timedelta(seconds=session_timeout)

# 会话的start不晚于upper_bound，且会话的end不早于lower_bound，即两个区间有重叠
matchingSession = models.SessionList.query.filter(and_(
    models.SessionList.session_start <= upper_bound,
    models.SessionList.session_end >= lower_bound
)).first()

2. 检查时区一致性 #

如果Python中的remote_stamp是带时区的datetime对象（tz-aware），而数据库中的datetime字段是无时区的（tz-naive），会导致时间对比出现偏差。

• 修复：统一时区配置，要么将Python的datetime转换为无时区的本地时间，要么配置SQLAlchemy启用时区支持。

3. 过滤会话表中的空值 #

会话表中session_start或session_end为NULL的记录，会导致条件判断返回UNKNOWN，无法匹配。查询时需过滤空值：

matchingSession = models.SessionList.query.filter(and_(
    models.SessionList.session_start.isnot(None),
    models.SessionList.session_end.isnot(None),
    # 上述时间匹配条件
)).first()

4. 手动验证SQL查询 #

取一个已知的remote_stamp（如样本中的2021-12-27 13:41:10），手动执行SQL查询验证是否有匹配结果：

SELECT * FROM session_list 
WHERE session_start <= '2021-12-27 14:41:10'  -- remote_stamp + 3600秒
  AND session_end >= '2021-12-27 12:41:10'; -- remote_stamp - 3600秒

如果该SQL能返回结果，说明是代码逻辑问题；如果无结果，需检查会话表中的数据是否符合匹配条件。

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