PostgreSQL中如何结合date_trunc与BETWEEN条件按截断小时筛选数据？

嘿，我来帮你搞定这个问题！你怀疑的两个点完全说到了点子上——直接拿timestamp和纯小时数对比、小时定义逻辑不对，确实是这类需求最容易踩的坑。咱们一步步拆解修正方案：

核心问题根源 #

你提到的两个疑点就是关键：

• (a) 类型不匹配：timestamp是包含日期+时间的完整时间类型，直接和9、18这种纯小时数值对比，数据库根本没法正确关联时间维度（它会把timestamp转成 epoch 数值，和小时数完全不在一个量级）；
• (b) 小时定义混乱：如果你的「小时」一会指纯小时数（0-23），一会指截断到小时的完整时间戳（比如2024-05-21 10:00:00），筛选逻辑自然会出错。

分场景修正方案 #

根据你的实际需求，分两种常见场景来处理：

场景1：筛选「某段连续小时区间内的完整时间数据」（比如当天9点到17点） #

核心思路：把timestamp截断到小时级的完整时间戳，再和同样是小时级的时间范围用BETWEEN匹配，保证两边类型完全一致。

举个MySQL的例子（8.0+版本）

SELECT *
FROM your_table
-- 把字段截断到小时级时间戳
WHERE DATE_TRUNC('hour', your_timestamp_col) BETWEEN 
    -- 起始时间：当天9点整
    CURRENT_DATE + INTERVAL 9 HOUR 
    -- 结束时间：当天17点整
    AND CURRENT_DATE + INTERVAL 17 HOUR;

如果是低版本MySQL（不支持DATE_TRUNC），可以用DATE_FORMAT转成字符串（注意两边都要转成相同格式）：

SELECT *
FROM your_table
WHERE DATE_FORMAT(your_timestamp_col, '%Y-%m-%d %H:00:00') BETWEEN 
    DATE_FORMAT(CURRENT_DATE, '%Y-%m-%d 09:00:00') 
    AND DATE_FORMAT(CURRENT_DATE, '%Y-%m-%d 17:00:00');

举个PostgreSQL的例子

SELECT *
FROM your_table
WHERE DATE_TRUNC('hour', created_at) BETWEEN 
    CURRENT_DATE + INTERVAL '9 hours' 
    AND CURRENT_DATE + INTERVAL '17 hours';

场景2：筛选「所有日期中，小时数在某个区间的数据」（比如所有早上8-10点的数据） #

如果不需要限制日期，只想提取所有符合小时数范围的记录，那就先从timestamp中提取纯小时数（0-23），再用BETWEEN对比。

MySQL示例

SELECT *
FROM your_table
-- 提取字段的小时数，对比8-10点
WHERE HOUR(your_timestamp_col) BETWEEN 8 AND 10;

PostgreSQL示例

SELECT *
FROM your_table
WHERE EXTRACT(HOUR FROM your_timestamp_col) BETWEEN 8 AND 10;

避坑提醒 #

• 别犯这种低级错误：your_timestamp_col BETWEEN 9 AND 17——这会让数据库把timestamp转成 epoch 数值（比如1716288000），和9、17完全不匹配；
• 尽量统一类型：如果用DATE_FORMAT转成字符串做筛选，范围值也要转成相同格式，避免数据库隐式转换导致的错误；
• 如果是跨天的小时范围（比如昨天22点到今天6点），直接写完整的小时级时间戳即可，比如BETWEEN '2024-05-20 22:00:00' AND '2024-05-21 06:00:00'。

如果你的数据库是其他类型（比如SQL Server），可以补充说明，我再调整适配的示例~

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