Hive中如何将Timestamp按小时取整？附具体示例

我来分享几个在Hive环境下把'2018-01-01 01:35:00.000'这类Timestamp数据按小时取整到'2018-01-01 01:00:00.000'的实用方案，都是日常工作里验证过的，放心用～

方法一：用trunc + date_format（最简洁高效） #

Hive的trunc函数专门用来截断日期时间，指定'HH'参数就能直接把时间砍到当前小时的起始点，再用date_format补全毫秒位的.000就行：

-- 测试单个值
SELECT date_format(trunc('2018-01-01 01:35:00.000', 'HH'), 'yyyy-MM-dd HH:mm:ss.SSS') AS hour_floor_timestamp;

-- 应用到数据表字段
SELECT date_format(trunc(your_timestamp_column, 'HH'), 'yyyy-MM-dd HH:mm:ss.SSS') AS hour_floor_timestamp
FROM your_target_table;

说明：trunc处理后会得到2018-01-01 01:00:00，date_format帮我们把格式补成带.000的完整Timestamp格式，完美匹配需求。

方法二：Unix时间戳计算法（兼容旧版Hive） #

如果你的Hive版本比较老，trunc可能不支持'HH'参数（新版本基本都支持了），可以用时间戳转换的方式绕一下：

-- 测试单个值
SELECT from_unixtime(
    unix_timestamp('2018-01-01 01:35:00.000') - unix_timestamp('2018-01-01 01:35:00.000') % 3600,
    'yyyy-MM-dd HH:mm:ss.SSS'
) AS hour_floor_timestamp;

-- 应用到数据表字段
SELECT from_unixtime(
    unix_timestamp(your_timestamp_column) - unix_timestamp(your_timestamp_column) % 3600,
    'yyyy-MM-dd HH:mm:ss.SSS'
) AS hour_floor_timestamp
FROM your_target_table;

原理：把Timestamp转成Unix时间戳（以秒为单位），减去当前时间戳对3600（1小时的秒数）取余的部分，就得到了当前小时起始的秒数，再转成目标格式即可。

方法三：字符串拼接法（直观易懂） #

如果觉得函数嵌套有点绕，也可以用更直观的字符串拼接思路：提取日期和小时部分，拼接成yyyy-MM-dd HH:00:00，再转成带毫秒的格式：

-- 应用到数据表字段
SELECT date_format(
    concat(date(your_timestamp_column), ' ', hour(your_timestamp_column), ':00:00'),
    'yyyy-MM-dd HH:mm:ss.SSS'
) AS hour_floor_timestamp
FROM your_target_table;

这个方法逻辑直白，新手也能快速理解，适合快速验证需求。

额外注意点 #

• 如果你的字段是字符串类型而非Timestamp，记得先转类型：cast(your_string_column AS timestamp)，再用上面的方法处理。
• 建议先拿单条数据测试SQL，确认结果符合预期后再全表运行，避免踩坑。

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