如何在SQL中以hh:mm:ss格式返回航班飞行时长？

报错原因 #

你遇到的错误是因为PostgreSQL没有内置的DATEDIFF函数（该函数常见于MySQL、SQL Server），同时你的子查询存在逻辑问题：该子查询返回两列数据，且如果flight表有多行，会返回多行结果，无法直接作为主查询SELECT列表中的单个字段值。

正确解决方案 #

要得到hh:mm:ss格式的飞行时长，我们可以利用PostgreSQL对时间戳的原生运算能力，结合格式化函数实现：

方法1：直接格式化时间间隔（适用于时长≤24小时的航班）

SELECT
    departure_city,
    arrival_city,
    -- 计算时间差并格式化为hh:mm:ss
    TO_CHAR(arrival_time - departure_time, 'HH24:MI:SS') AS flight_duration
FROM flight;

方法2：处理超过24小时的时长（通用方案）

如果存在飞行时长超过24小时的航班，上面的方法会显示天数（比如1 02:30:00），可以通过计算总秒数再转换为时分秒：

SELECT
    departure_city,
    arrival_city,
    TO_CHAR(
        MAKE_INTERVAL(seconds => EXTRACT(EPOCH FROM (arrival_time - departure_time))),
        'HH24:MI:SS'
    ) AS flight_duration
FROM flight;

方法3：手动拼接时分秒（更灵活）

通过提取小时、分钟、秒分量，手动拼接成目标格式：

SELECT
    departure_city,
    arrival_city,
    CONCAT(
        LPAD(CAST(EXTRACT(HOUR FROM (arrival_time - departure_time)) AS TEXT), 2, '0'),
        ':',
        LPAD(CAST(EXTRACT(MINUTE FROM (arrival_time - departure_time)) AS TEXT), 2, '0'),
        ':',
        LPAD(CAST(ROUND(EXTRACT(SECOND FROM (arrival_time - departure_time))) AS TEXT), 2, '0')
    ) AS flight_duration
FROM flight;

原SQL的其他问题修正 #

原SQL中的子查询完全多余，主查询中可以直接对departure_time和arrival_time进行类型转换，比如：

SELECT
    departure_city,
    arrival_city,
    TO_CHAR(arrival_time - departure_time, 'HH24:MI:SS') AS flight_duration,
    CAST(arrival_time AS TIME) AS arrival_time_only,
    CAST(departure_time AS TIME) AS departure_time_only
FROM flight;

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