如何用WHERE子句基于日/月/年过滤Unix时间戳数据？

嘿，我来帮你搞定这个Unix时间戳的筛选问题！首先得弄明白你之前用WHERE day=5报错的原因，再针对不同筛选需求给出具体的SQL写法，还会分常见数据库举例哦～

为啥直接用WHERE day=5会报错？ #

大概率是你写了类似这样的SQL：

SELECT name, DAY(FROM_UNIXTIME(date_time_stamp)) AS day
FROM your_table
WHERE day=5;

SQL的执行顺序是先跑WHERE再处理SELECT，等执行WHERE的时候，SELECT里定义的day别名还没生成呢，数据库自然不知道这个字段是什么。解决办法要么在WHERE里重复写日期提取逻辑，要么用子查询/CTE先转换好字段再筛选。

不同筛选需求的SQL写法 #

核心逻辑都是先把Unix时间戳（注意是秒还是毫秒，默认按秒处理）转换成数据库能识别的日期类型，再提取年/月/日或者用时间戳范围匹配（后者更高效，能用到索引）。

1. MySQL/MariaDB 示例 #

筛选5月或6月的记录

-- 方式1：提取月份（简单直观）
SELECT name, date_time_stamp, FROM_UNIXTIME(date_time_stamp) AS full_date
FROM your_table
WHERE MONTH(FROM_UNIXTIME(date_time_stamp)) IN (5, 6);

-- 方式2：时间戳范围（推荐，数据量大时更快）
SELECT name, date_time_stamp, FROM_UNIXTIME(date_time_stamp) AS full_date
FROM your_table
WHERE date_time_stamp BETWEEN UNIX_TIMESTAMP('2024-05-01 00:00:00') 
                          AND UNIX_TIMESTAMP('2024-06-30 23:59:59');

筛选每月5号的记录

SELECT name, date_time_stamp, FROM_UNIXTIME(date_time_stamp) AS full_date
FROM your_table
WHERE DAY(FROM_UNIXTIME(date_time_stamp)) = 5;

筛选指定年月日（比如2024-05-05）

-- 方式1：日期匹配
SELECT name, date_time_stamp, FROM_UNIXTIME(date_time_stamp) AS full_date
FROM your_table
WHERE DATE(FROM_UNIXTIME(date_time_stamp)) = '2024-05-05';

-- 方式2：时间戳范围（高效）
SELECT name, date_time_stamp, FROM_UNIXTIME(date_time_stamp) AS full_date
FROM your_table
WHERE date_time_stamp BETWEEN UNIX_TIMESTAMP('2024-05-05 00:00:00') 
                          AND UNIX_TIMESTAMP('2024-05-05 23:59:59');

2. PostgreSQL 示例 #

筛选5月或6月的记录

-- 方式1：提取月份
SELECT name, date_time_stamp, TO_TIMESTAMP(date_time_stamp) AS full_date
FROM your_table
WHERE EXTRACT(MONTH FROM TO_TIMESTAMP(date_time_stamp)) IN (5, 6);

-- 方式2：时间戳范围
SELECT name, date_time_stamp, TO_TIMESTAMP(date_time_stamp) AS full_date
FROM your_table
WHERE date_time_stamp BETWEEN EXTRACT(EPOCH FROM '2024-05-01 00:00:00'::timestamp)
                          AND EXTRACT(EPOCH FROM '2024-06-30 23:59:59'::timestamp);

筛选每月5号的记录

SELECT name, date_time_stamp, TO_TIMESTAMP(date_time_stamp) AS full_date
FROM your_table
WHERE EXTRACT(DAY FROM TO_TIMESTAMP(date_time_stamp)) = 5;

筛选指定年月日

-- 方式1：日期匹配
SELECT name, date_time_stamp, TO_TIMESTAMP(date_time_stamp) AS full_date
FROM your_table
WHERE TO_CHAR(TO_TIMESTAMP(date_time_stamp), 'YYYY-MM-DD') = '2024-05-05';

-- 方式2：时间戳范围
SELECT name, date_time_stamp, TO_TIMESTAMP(date_time_stamp) AS full_date
FROM your_table
WHERE date_time_stamp BETWEEN EXTRACT(EPOCH FROM '2024-05-05 00:00:00'::timestamp)
                          AND EXTRACT(EPOCH FROM '2024-05-05 23:59:59'::timestamp);

3. SQL Server 示例 #

SQL Server需要把Unix时间戳转成datetime，用DATEADD(SECOND, 时间戳, '1970-01-01')：

筛选5月或6月的记录

-- 方式1：提取月份
SELECT name, date_time_stamp, DATEADD(SECOND, date_time_stamp, '1970-01-01') AS full_date
FROM your_table
WHERE MONTH(DATEADD(SECOND, date_time_stamp, '1970-01-01')) IN (5, 6);

-- 方式2：时间戳范围
SELECT name, date_time_stamp, DATEADD(SECOND, date_time_stamp, '1970-01-01') AS full_date
FROM your_table
WHERE date_time_stamp BETWEEN DATEDIFF(SECOND, '1970-01-01', '2024-05-01 00:00:00')
                          AND DATEDIFF(SECOND, '1970-01-01', '2024-06-30 23:59:59');

筛选每月5号的记录

SELECT name, date_time_stamp, DATEADD(SECOND, date_time_stamp, '1970-01-01') AS full_date
FROM your_table
WHERE DAY(DATEADD(SECOND, date_time_stamp, '1970-01-01')) = 5;

筛选指定年月日

-- 方式1：日期匹配
SELECT name, date_time_stamp, DATEADD(SECOND, date_time_stamp, '1970-01-01') AS full_date
FROM your_table
WHERE CAST(DATEADD(SECOND, date_time_stamp, '1970-01-01') AS DATE) = '2024-05-05';

-- 方式2：时间戳范围
SELECT name, date_time_stamp, DATEADD(SECOND, date_time_stamp, '1970-01-01') AS full_date
FROM your_table
WHERE date_time_stamp BETWEEN DATEDIFF(SECOND, '1970-01-01', '2024-05-05 00:00:00')
                          AND DATEDIFF(SECOND, '1970-01-01', '2024-05-05 23:59:59');

小提醒 #

• 如果你的时间戳是毫秒级的（比如1622505600000），记得转换时除以1000，比如MySQL里用FROM_UNIXTIME(date_time_stamp/1000)。
• 优先用时间戳范围匹配的写法，这种方式能利用date_time_stamp字段的索引，查询效率比提取日期字段高很多。

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