两条查询2020年用户最后登录时间SQL的差异原因分析

第二条语句结果错误的根源是时间筛选条件的边界覆盖范围不一致，具体差异如下：

• 第一条语句的筛选逻辑
  用year(time_stamp) = '2020'做筛选时，数据库会提取时间字段的年份值做判断，只要记录的时间属于2020自然年就会被纳入统计，完整覆盖2020-01-01 00:00:00到2020-12-31 23:59:59.999...（对应字段支持的最高时间精度）的所有数据，没有遗漏。
• 第二条语句的筛选逻辑
  用time_stamp between '2020-01-01' and '2020-12-31'做筛选时，因为time_stamp一般是带时分秒、毫秒/微秒精度的DATETIME/TIMESTAMP类型，数据库会隐式把传入的日期字符串转为对应日期的零点时间：
  • 下界'2020-01-01'转为2020-01-01 00:00:00
  • 上界'2020-12-31'转为2020-12-31 00:00:00
between是左右都包含的闭区间匹配，最终筛选的时间范围只到2020年12月31日零点为止，当天零点之后的所有2020年登录记录全被过滤掉了，自然会导致最后计算的最大登录时间结果错误。

补充：如果想用范围匹配实现和第一条语句一致的筛选效果，同时还能利用时间字段的索引提升查询效率，正确写法是用左闭右开的区间判断：

select
    user_id,
    max(time_stamp) as "last_stamp"
from
    logins
where
    time_stamp >= '2020-01-01' 
    and time_stamp < '2021-01-01'
group by
    user_id

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