Oracle SQL中如何将时分秒时间戳转数值并反向转换？

一、将时分秒字符串转换为整数（推荐用总秒数计算） #

直接把时间字符串转为TIME类型，再提取总秒数，这是最适合计算平均耗时的方式——总秒数能准确反映时长，避免拼接数值的逻辑误差：

-- 单值转换示例
SELECT EXTRACT(EPOCH FROM CAST('01:54:45' AS TIME))::BIGINT AS total_seconds;
-- 结果：6885（对应1*3600 + 54*60 +45 = 6885秒）

-- 针对列的转换（假设列名为time_col）
SELECT EXTRACT(EPOCH FROM CAST(time_col AS TIME))::BIGINT AS total_seconds
FROM your_table;

如果坚持要用HHMISS拼接的数值（比如把01:54:45转为15445），必须先将字符串转为TIME类型再格式化，注意指定24小时制格式：

SELECT TO_NUMBER(TO_CHAR(CAST('01:54:45' AS TIME), 'HH24MISS'), '999999') AS time_num;
-- 结果：15445

你之前失败的原因大概率是没先把字符串转为TIME类型，直接对字符串用TO_CHAR会报错。

二、将平均数值转回时分秒格式 #

情况1：基于总秒数转换 #

如果计算平均用的是总秒数，直接把数值转为时间间隔再格式化：

-- 示例：将3803秒转为'01:03:23'
SELECT TO_CHAR(MAKE_INTERVAL(SECONDS => 3803), 'HH24:MI:SS') AS time_str;
-- 结果：'01:03:23'

-- 针对平均结果的转换（假设avg_seconds是平均后的总秒数列）
SELECT TO_CHAR(MAKE_INTERVAL(SECONDS => avg_seconds), 'HH24:MI:SS') AS avg_time_str
FROM (
    SELECT AVG(total_seconds) AS avg_seconds
    FROM your_table
    WHERE created_at BETWEEN NOW() - INTERVAL '3 days' AND NOW()
) AS avg_result;

情况2：基于HHMISS拼接的数值转换 #

如果用的是拼接数值，需拆分出时、分、秒再格式化：

-- 示例：将15445转为'01:54:45'
SELECT 
    LPAD((time_num / 10000)::TEXT, 2, '0') || ':' ||
    LPAD(((time_num % 10000) / 100)::TEXT, 2, '0') || ':' ||
    LPAD((time_num % 100)::TEXT, 2, '0') AS time_str
FROM (SELECT 15445 AS time_num) AS t;
-- 结果：'01:54:45'

注意事项 #

• 优先用总秒数计算平均，拼接数值存在逻辑缺陷：比如01:00:00和00:60:00的拼接数值分别是10000和6000，但实际时长完全相同。
• 若使用其他数据库，语法会略有差异，但核心逻辑一致：先转时间类型提取总秒数/拆分时分秒；转回时反向计算或格式化时间间隔。

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