如何在PostgreSQL函数中计算timestamptz的毫秒级时间差并修复运算符报错

错误原因 #

你的函数逻辑中括号位置错误，触发了类型不匹配：你实际执行的是 timestamptz类型值 - 双精度类型的秒数，PostgreSQL没有对应运算的内置运算符，因此抛出报错。

修复后的函数代码 #

CREATE OR REPLACE FUNCTION _get_date_diff_ms(p_begin_time timestamptz, p_end_time timestamptz)
RETURNS int AS
$$
BEGIN
    -- 先计算两个时间的差值得到interval，再转成秒级时间戳乘以1000取整
    RETURN ROUND(
        EXTRACT(EPOCH FROM (p_end_time - p_begin_time)) * 1000
    )::int;
END;
$$ LANGUAGE plpgsql;

验证调用 #

你可以用下面的语句验证效果：

-- 两个相同时间差为0
select _get_date_diff_ms(NOW(), NOW());
-- 模拟1秒差，返回约1000
select _get_date_diff_ms(NOW(), NOW() + INTERVAL '1 second');

可选优化方案 #

如果不需要复杂的PL/pgSQL逻辑，你可以直接用SQL函数实现，执行效率更高：

CREATE OR REPLACE FUNCTION _get_date_diff_ms(p_begin_time timestamptz, p_end_time timestamptz)
RETURNS int AS
$$
SELECT ROUND(EXTRACT(EPOCH FROM (p_end_time - p_begin_time)) * 1000)::int;
$$ LANGUAGE sql STABLE;

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