PostgreSQL中秒数转年/月/日/时/分/秒格式的异常问题

问题场景 #

你需要将秒数转换为包含年、月、日、时、分、秒的格式化字符串，但当前使用的SQL语句无法自动将小时进位到更高时间单位，输出不符合预期：

尝试的SQL语句：

SELECT TO_CHAR((1670857661 || ' second')::interval, 'YYYY" years" MM" mons "DD" days "HH24" hours "MI" mins "SS" secs"') 

当前输出：

0000 years 00 mons 00 days 464127 hours 07 mins 41 secs

期望输出：

54 years 8 mons 12 days 10 hours 51 mins 12 secs

问题原因 #

PostgreSQL中直接将秒转换为interval类型时，只会将秒拆解为秒、分、小时，不会自动进位到天、月、年——因为interval的存储是独立的时间单位字段，而非基于固定时长的累加计算，无法处理年、月这类时长不固定的单位。

解决方案 #

通过基准日期+AGE()函数计算实际日期差值，AGE()会自动处理年、月的进位逻辑（考虑闰年、不同月份天数等），再提取各时间单位拼接成目标格式：

WITH base_params AS (
    SELECT 
        '1970-01-01'::TIMESTAMP AS start_dt,  -- 可自定义基准日期
        1670857661 AS total_secs
),
calc_date AS (
    SELECT start_dt + (total_secs || ' second')::INTERVAL AS end_dt
    FROM base_params
)
SELECT
    EXTRACT(YEAR FROM AGE(end_dt, start_dt)) || ' years ' ||
    EXTRACT(MONTH FROM AGE(end_dt, start_dt)) || ' mons ' ||
    EXTRACT(DAY FROM AGE(end_dt, start_dt)) || ' days ' ||
    EXTRACT(HOUR FROM AGE(end_dt, start_dt)) || ' hours ' ||
    EXTRACT(MINUTE FROM AGE(end_dt, start_dt)) || ' mins ' ||
    FLOOR(EXTRACT(SECOND FROM AGE(end_dt, start_dt))) || ' secs' AS formatted_duration
FROM calc_date;

说明 #

• AGE(end_dt, start_dt)：计算两个日期的时间差，自动处理年、月、日的进位
• EXTRACT()：从时间差中提取对应单位的数值
• FLOOR()：对秒数取整，避免出现小数

如果不需要考虑实际日期的差异（比如按固定365天/年、30天/月计算），可以用纯数值计算（精度较低）：

WITH total_secs AS (SELECT 1670857661 AS secs)
SELECT
    FLOOR(secs / (365*24*3600)) || ' years ' ||
    FLOOR((secs % (365*24*3600)) / (30*24*3600)) || ' mons ' ||
    FLOOR((secs % (30*24*3600)) / (24*3600)) || ' days ' ||
    FLOOR((secs % (24*3600)) / 3600) || ' hours ' ||
    FLOOR((secs % 3600) / 60) || ' mins ' ||
    (secs % 60) || ' secs' AS formatted_duration
FROM total_secs;

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