如何转换timestamp字段datediff结果为数值以应用average()函数

你之前得到负数结果是因为计算顺序写反了，应该用end减去start，再结合你使用的数据库选择对应的转换方法即可：

各主流数据库实现代码 #

1. Oracle #

如果是timestamp类型，两种写法可选：

• 转DATE类型计算（得到整数秒）

SELECT
    (CAST(end AS DATE) - CAST(start AS DATE)) * 86400 AS duration
FROM my_table;

• 保留小数秒写法

SELECT
    EXTRACT(DAY FROM (end - start)) * 86400 +
    EXTRACT(HOUR FROM (end - start)) * 3600 +
    EXTRACT(MINUTE FROM (end - start)) * 60 +
    EXTRACT(SECOND FROM (end - start)) AS duration
FROM my_table;

2. MySQL / MariaDB #

直接用内置TIMESTAMPDIFF函数，第一个参数指定单位即可：

-- 单位为秒，返回整数结果
SELECT TIMESTAMPDIFF(SECOND, start, end) AS duration FROM my_table;
-- 要分钟单位就把SECOND改为MINUTE

3. PostgreSQL #

提取时间差的epoch值（即总秒数）：

SELECT EXTRACT(EPOCH FROM (end - start))::INT AS duration FROM my_table;

4. SQL Server #

用DATEDIFF函数实现：

SELECT DATEDIFF(SECOND, start, end) AS duration FROM my_table;

后续聚合计算示例 #

转换为数值类型的duration后，即可直接做均值、中位数等聚合，以下为按年统计的示例（以PostgreSQL为例）：

SELECT
    EXTRACT(YEAR FROM start) AS stat_year,
    AVG(duration) AS avg_duration_sec,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY duration) AS median_duration_sec
FROM (
    SELECT 
        start,
        EXTRACT(EPOCH FROM (end - start))::INT AS duration
    FROM my_table
) t
GROUP BY stat_year;

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