如何在Hive与Impala中计算两个时间戳的秒级和毫秒级差值

针对你遇到的字符串时间戳（格式yyyy-MM-dd'T'HH:mm:ss.SSS）差值计算忽略毫秒的问题，核心是要完整解析时间戳中的毫秒部分，再进行差值计算。以下是CDP集群中常用的Hive和Spark SQL实现方案：

Hive SQL 实现 #

Hive中需通过to_timestamp指定完整格式解析时间，再转换为带毫秒精度的数值类型计算差值：

SELECT
  t1,
  t2,
  -- 带毫秒精度的秒级差值（如1.147秒）
  (cast(to_timestamp(t1, 'yyyy-MM-dd\'T\'HH:mm:ss.SSS') as double) - cast(to_timestamp(t2, 'yyyy-MM-dd\'T\'HH:mm:ss.SSS') as double)) AS diff_seconds,
  -- 整数秒级差值（向下取整，如1秒）
  floor((cast(to_timestamp(t1, 'yyyy-MM-dd\'T\'HH:mm:ss.SSS') as double) - cast(to_timestamp(t2, 'yyyy-MM-dd\'T\'HH:mm:ss.SSS') as double))) AS diff_seconds_int,
  -- 毫秒级差值（如1147毫秒）
  (cast(to_timestamp(t1, 'yyyy-MM-dd\'T\'HH:mm:ss.SSS') as double) - cast(to_timestamp(t2, 'yyyy-MM-dd\'T\'HH:mm:ss.SSS') as double)) * 1000 AS diff_milliseconds
FROM your_table;

Spark SQL 实现 #

Spark SQL可通过拆分秒级时间戳和毫秒部分，组合后计算高精度差值：

SELECT
  t1,
  t2,
  -- 带毫秒精度的秒级差值
  (unix_timestamp(to_timestamp(t1, 'yyyy-MM-dd\'T\'HH:mm:ss.SSS')) + date_part('millisecond', to_timestamp(t1, 'yyyy-MM-dd\'T\'HH:mm:ss.SSS'))/1000) 
  - (unix_timestamp(to_timestamp(t2, 'yyyy-MM-dd\'T\'HH:mm:ss.SSS')) + date_part('millisecond', to_timestamp(t2, 'yyyy-MM-dd\'T\'HH:mm:ss.SSS'))/1000) AS diff_seconds,
  -- 毫秒级差值
  (unix_timestamp(to_timestamp(t1, 'yyyy-MM-dd\'T\'HH:mm:ss.SSS')) * 1000 + date_part('millisecond', to_timestamp(t1, 'yyyy-MM-dd\'T\'HH:mm:ss.SSS')))
  - (unix_timestamp(to_timestamp(t2, 'yyyy-MM-dd\'T\'HH:mm:ss.SSS')) * 1000 + date_part('millisecond', to_timestamp(t2, 'yyyy-MM-dd\'T\'HH:mm:ss.SSS'))) AS diff_milliseconds
FROM your_table;

关键说明 #

之前的方案忽略毫秒，通常是因为：

• 未指定完整的时间格式，导致解析时自动截断毫秒部分；
• 使用了仅返回秒级整数的unix_timestamp直接计算，丢失了毫秒精度。

用上述方案处理你的示例数据，第一行将得到diff_seconds=1.147、diff_milliseconds=1147；第二行得到diff_seconds=86401.998、diff_milliseconds=86401998，完全保留毫秒精度。

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