SQL中如何基于timestamp字段筛选指定年份的数据

问题原因&正确解法 #

你之前的写法存在3个核心错误：

1. SQL执行顺序中WHERE阶段早于SELECT阶段，无法直接读取SELECT中定义的别名（如date_value、未定义的year）
2. 日期值没有加引号包裹，2000-01-01会被SQL识别为数值运算（2000减1减1=1998），无法正确匹配日期
3. timestamp是SQL保留关键字，直接作为字段名使用容易触发语法错误，建议用反引号包裹

解法1：简单易读（适合小数据量场景）

直接在WHERE中对时间戳转格式后提取年份判断：

SELECT rating, `timestamp`,
FROM_UNIXTIME(`timestamp`) as datetime,
CAST(FROM_UNIXTIME(`timestamp`) as date) AS date_value
FROM ratings
WHERE YEAR(FROM_UNIXTIME(`timestamp`)) = 2000;

解法2：性能最优（适合大数据量、timestamp字段有索引的场景）

直接用时间戳范围筛选，无需逐行计算函数，可以命中索引大幅提升查询效率：

SELECT rating, `timestamp`,
FROM_UNIXTIME(`timestamp`) as datetime,
CAST(FROM_UNIXTIME(`timestamp`) as date) AS date_value
FROM ratings
WHERE `timestamp` >= UNIX_TIMESTAMP('2000-01-01 00:00:00')
AND `timestamp` < UNIX_TIMESTAMP('2001-01-01 00:00:00');

之前写法失效的具体说明

• WHERE year=2000：既没有定义year别名，WHERE也无法读取SELECT阶段的别名，直接报错
• WHERE date_value >= 2000-01-01 AND date_value <= 2000-12-31：WHERE读不到date_value别名，且日期没有加引号被识别为数值，匹配不到任何结果
• HAVING date_value >= 2000-01-01 AND date_value <= 2000-12-31：HAVING只能用于分组后过滤，未使用GROUP BY时效率极低，同时日期未加引号也无法正确匹配
• HAVING year=2000：未定义year别名，无法匹配

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