Hive中计算两个Epoch时间戳的时分秒差值及写入问题

首先得戳破核心问题：你试图把任务时长（时间间隔）写入定义为timestamp类型的列里，这本质是类型完全不匹配——timestamp是用来存储具体的时间点（比如2024-05-20 14:30:00.000），而不是时长（比如02 03:15:40:500），Hive根本没法把时长格式的字符串解析成合法的timestamp，这就是写入报错的原因。

下面分两步帮你解决：

1. 正确计算并格式化任务时长 #

假设你的T1表中，start_epoch和end_epoch是秒级的Epoch时间戳（如果是毫秒级，调整计算逻辑即可），用以下SQL就能算出并格式化为dd hh:mm:ss:ms格式：

SELECT
  -- 保留其他需要写入T2的列
  concat(
    -- 计算天数
    cast(floor((end_epoch - start_epoch) / 86400) as string), ' ',
    -- 计算小时，补前导0到2位
    lpad(cast(floor(((end_epoch - start_epoch) % 86400) / 3600) as string), 2, '0'), ':',
    -- 计算分钟，补前导0到2位
    lpad(cast(floor(((end_epoch - start_epoch) % 3600) / 60) as string), 2, '0'), ':',
    -- 计算秒，补前导0到2位
    lpad(cast(floor((end_epoch - start_epoch) % 60) as string), 2, '0'), ':',
    -- 计算毫秒，补前导0到3位（秒级Epoch转毫秒取模）
    lpad(cast(((end_epoch - start_epoch) * 1000) % 1000 as string), 3, '0')
  ) as duration_str
FROM T1;

如果是毫秒级Epoch时间戳，计算逻辑改成这样：

SELECT
  concat(
    cast(floor((end_epoch - start_epoch) / 86400000) as string), ' ',
    lpad(cast(floor(((end_epoch - start_epoch) % 86400000) / 3600000) as string), 2, '0'), ':',
    lpad(cast(floor(((end_epoch - start_epoch) % 3600000) / 60000) as string), 2, '0'), ':',
    lpad(cast(floor(((end_epoch - start_epoch) % 60000) / 1000) as string), 2, '0'), ':',
    lpad(cast((end_epoch - start_epoch) % 1000 as string), 3, '0')
  ) as duration_str
FROM T1;

2. 调整T2表的列类型适配时长存储 #

因为timestamp天生不适合存时长，你得修改T2的duration列类型，推荐两种实用方案：

方案一：用string存格式化后的时长 #

直接把duration列改成string，就能直接存储你要的dd hh:mm:ss:ms格式：

-- 若T2已存在，修改列类型
ALTER TABLE T2 CHANGE COLUMN duration duration string;

-- 若新建T2，定义列时用string
CREATE TABLE T2 (
  -- 其他列定义...
  duration string
);

然后执行插入：

INSERT INTO T2
SELECT
  -- 其他列...
  concat(
    cast(floor((end_epoch - start_epoch) / 86400) as string), ' ',
    lpad(cast(floor(((end_epoch - start_epoch) % 86400) / 3600) as string), 2, '0'), ':',
    lpad(cast(floor(((end_epoch - start_epoch) % 3600) / 60) as string), 2, '0'), ':',
    lpad(cast(floor((end_epoch - start_epoch) % 60) as string), 2, '0'), ':',
    lpad(cast(((end_epoch - start_epoch) * 1000) % 1000 as string), 3, '0')
  ) as duration
FROM T1;

方案二：用数值类型存总时长（方便后续计算） #

如果之后还要对时长做统计、排序这类操作，推荐用bigint存总秒数（或总毫秒数），查询时再格式化：

-- 修改T2列类型为bigint
ALTER TABLE T2 CHANGE COLUMN duration duration bigint;

-- 插入总秒数（毫秒级直接存end_epoch - start_epoch）
INSERT INTO T2
SELECT
  -- 其他列...
  end_epoch - start_epoch as duration
FROM T1;

查询时再转成你要的格式：

SELECT
  -- 其他列...
  concat(
    cast(floor(duration / 86400) as string), ' ',
    lpad(cast(floor((duration % 86400) / 3600) as string), 2, '0'), ':',
    lpad(cast(floor((duration % 3600) / 60) as string), 2, '0'), ':',
    lpad(cast(floor(duration % 60) as string), 2, '0'), ':',
    lpad(cast((duration * 1000) % 1000 as string), 3, '0')
  ) as duration_str
FROM T2;

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