如何在PostgreSQL中创建模仿SQL Server TODATETIMEOFFSET的函数？

可以直接通过时间转换逻辑实现该函数，无需依赖时区名称查询或修改会话时区，以下是正确的实现方案：

函数实现 #

我们提供两个重载版本，分别支持带时区和不带时区的时间输入：

-- 处理带时区时间的版本
CREATE OR REPLACE FUNCTION TODATETIMEOFFSET(
  p_ts TIMESTAMPTZ,
  p_offset VARCHAR
) RETURNS TIMESTAMPTZ AS $$
BEGIN
  -- 将带时区时间转成UTC无时区时间，加上偏移量后再转为指定偏移的带时区时间
  RETURN (p_ts AT TIME ZONE 'UTC' + p_offset::INTERVAL) AT TIME ZONE p_offset;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 处理无时区时间的版本
CREATE OR REPLACE FUNCTION TODATETIMEOFFSET(
  p_ts TIMESTAMP,
  p_offset VARCHAR
) RETURNS TIMESTAMPTZ AS $$
BEGIN
  -- 将无时区时间视为指定偏移下的本地时间，直接转为带时区时间
  RETURN p_ts AT TIME ZONE p_offset;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

测试验证 #

执行你提供的测试语句：

select current_timestamp, TODATETIMEOFFSET(current_timestamp,'-03:00');

假设当前UTC时间为2023-07-12 16:54:11.930，返回结果会是：

current_timestamp           |       todatetimeoffset
----------------------------------------+-------------------------------
 2023-07-12 16:54:11.930 +00:00        | 2023-07-12 13:54:11.930 -03:00

完全符合你的需求。

原代码问题分析 #

你之前的代码存在几个关键问题：

• 参数命名错误：timestamp_without_timezone TIMESTAMPTZ中参数名与类型名重复，属于语法错误。
• 时区名称查询不可靠：通过pg_timezone_names查找对应偏移的时区名称，同一个偏移可能对应多个时区，部分自定义偏移甚至没有匹配的命名时区，会导致函数执行失败或结果不确定。
• 不必要的会话时区修改：使用SET LOCAL TIME ZONE修改函数内会话时区属于冗余操作，直接通过时间转换即可实现需求。
• 类型转换错误：将结果存储为varchar再返回，会引入不必要的类型转换，可能丢失精度或出现格式问题。

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