PostgreSQL中如何从JSON数据计算时间差（分/秒）

修改后的查询语句 #

WITH tasks_length AS (
    SELECT metadata#>'{data,videoMetadata}' AS time 
    FROM "table"
), 
steptwo AS (
    SELECT json_array_elements(time::json) AS elements    
    FROM tasks_length
) 
SELECT  
    -- 转换JSON字符串为timestamp类型
    (elements ->> 'toTimestamp')::timestamp AS toTimestamp,
    (elements ->> 'fromTimestamp')::timestamp AS fromTimestamp,
    -- 计算总分钟数
    EXTRACT(EPOCH FROM ((elements ->> 'toTimestamp')::timestamp - (elements ->> 'fromTimestamp')::timestamp)) / 60 AS duration_minutes,
    -- 计算总秒数
    EXTRACT(EPOCH FROM ((elements ->> 'toTimestamp')::timestamp - (elements ->> 'fromTimestamp')::timestamp)) AS duration_seconds,
    -- 格式化显示为「XX分钟XX秒」
    TO_CHAR((elements ->> 'toTimestamp')::timestamp - (elements ->> 'fromTimestamp')::timestamp, 'MI"分钟"SS"秒"') AS duration_formatted
FROM steptwo;

核心逻辑说明 #

• JSON转时间戳：用->>替代原语句的->，直接提取JSON字段的文本值，再通过::timestamp转换为PostgreSQL可识别的时间戳类型。
• 时间差计算：
  1. 两个时间戳直接相减得到interval类型的时间间隔；
  2. EXTRACT(EPOCH FROM interval)将时间间隔转为总秒数，除以60即可得到总分钟数；
  3. 若需要格式化的分钟/秒展示，用TO_CHAR函数指定输出格式即可。

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