PostgreSQL中计算两个timestamptz差值（考虑时区）并写入第三列

你当前的表结构存在2个基础错误，以及1个字段类型选型问题，先修正再实现时长计算逻辑：

• 约束和主键引用的scheduled_departure_time、scheduled_arrival_time、scheduled_duration字段在表中未定义，属于列名不匹配，SQL无法正常执行
• duration字段使用TIME类型是错误选型：TIME用于存储一天内的时间点，取值范围仅00:00:00-24:00:00，无法存储跨天的长途飞行时长，必须改用PostgreSQL专门存储时间跨度的INTERVAL类型
• 时区问题不需要额外手动处理：TIMESTAMPTZ内部以UTC格式存储绝对时间戳，两个TIMESTAMPTZ值做减法时，会自动计算两个绝对时间点的真实间隔，和会话时区、起降地时区无关，不会出现时差导致的计算错误

步骤1：创建正确的表结构 #

如果使用PostgreSQL 12及以上版本，直接用存储生成列实现自动计算时长，插入/更新数据时不需要手动给duration赋值，数据库会自动完成计算：

CREATE TABLE duration
(
  departure_time TIMESTAMPTZ NOT NULL, 
  arrival_time TIMESTAMPTZ NOT NULL, 
  -- INTERVAL类型存时长，自动通过两个时间戳相减生成
  duration INTERVAL NOT NULL GENERATED ALWAYS AS (arrival_time - departure_time) STORED,
  flight_id INT UNIQUE NOT NULL,

  CHECK (duration > INTERVAL '0 seconds'), -- 约束时长为正
  CHECK (arrival_time > departure_time), -- 约束到达时间晚于起飞时间

  FOREIGN KEY (flight_id) REFERENCES flight(flight_id),
  PRIMARY KEY (flight_id) -- flight_id已唯一非空，直接作为主键比联合主键更合理
);

如果使用PostgreSQL 12以下版本，不支持生成列，用触发器实现自动计算即可：

-- 先建表，duration字段不用生成列配置
CREATE TABLE duration
(
  departure_time TIMESTAMPTZ NOT NULL, 
  arrival_time TIMESTAMPTZ NOT NULL, 
  duration INTERVAL NOT NULL,
  flight_id INT UNIQUE NOT NULL,

  CHECK (duration > INTERVAL '0 seconds'),
  CHECK (arrival_time > departure_time),

  FOREIGN KEY (flight_id) REFERENCES flight(flight_id),
  PRIMARY KEY (flight_id)
);

-- 创建计算时长的触发器函数
CREATE OR REPLACE FUNCTION calc_flight_duration()
RETURNS TRIGGER AS $$
BEGIN
  NEW.duration = NEW.arrival_time - NEW.departure_time;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器，插入、更新时自动计算duration值
CREATE TRIGGER trg_calc_duration
BEFORE INSERT OR UPDATE ON duration
FOR EACH ROW EXECUTE FUNCTION calc_flight_duration();

步骤2：格式化为6h 30m形式输出 #

INTERVAL类型存储的时长可以通过函数格式化输出，分两种场景选择写法：

场景1：兼容任意时长（包括跨天超过24小时的国际航班） #

通过总秒数换算小时和分钟，不会出现24小时取模的问题，分钟数自动补0：

SELECT
  flight_id,
  departure_time,
  arrival_time,
  concat(
    EXTRACT(EPOCH FROM duration)::INT / 3600,
    'h ',
    LPAD(((EXTRACT(EPOCH FROM duration)::INT % 3600) / 60)::TEXT, 2, '0'),
    'm'
  ) AS formatted_duration
FROM duration;

比如25小时5分的时长会输出为25h 05m，6小时30分输出为6h 30m。

场景2：所有航班时长均小于24小时 #

可以直接用to_char简化写法：

SELECT
  flight_id,
  to_char(duration, 'HH24"h" MI"m"') AS formatted_duration
FROM duration;

验证说明 #

插入测试数据验证时区计算正确性：

-- 起飞时间为东八区8点，到达时间为东五区14点半（对应东八区17点半，真实时长9小时30分）
INSERT INTO duration (departure_time, arrival_time, flight_id)
VALUES ('2024-06-01 08:00:00+08', '2024-06-01 14:30:00+05', 1);

查询得到的duration值为9 hours 30 minutes，格式化后为9h 30m，和真实飞行时长一致，没有受到时区差异影响。

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