五月 18, 2023
Oracle 有一个函数 SYS_EXTRACT_UTC，是将一个 timestamptz 转换成 UTC 时区的本地时间。
Oracle 示例
SELECT SYS_EXTRACT_UTC(TIMESTAMP '2000-03-28 11:30:00.00 -08:00')
FROM DUAL;
SYS_EXTRACT_UTC(TIMESTAMP'2000-03-2811:30:00.00-08:00')
-----------------------------------------------------------------
28-MAR-00 07.30.00 PM
PostgreSQL SYS_EXTRACT_UTC
执行 CREATE FUNCTION 语句创建函数如下：
CREATE OR REPLACE FUNCTION sys_extract_utc(ts timestamp with time zone)
RETURNS timestamp without time zone
STRICT
AS $$
declare
res timestamp;
begin
set local timezone to UTC;
select ts::timestamp into res;
return res;
end;
$$ LANGUAGE plpgsql;
验证：
postgres=# select sys_extract_utc('2000-03-28 11:30:00.00-08:00');
sys_extract_utc
---------------------
2000-03-28 19:30:00
(1 row)