PostgreSQL Oracle 兼容性之 - TZ_OFFSET
背景
Oracle提供的TZ_OFFSET函数,目的是将时区别名转换为以UTC为标准的OFFSET。
例如
SELECT TZ_OFFSET('US/Eastern') FROM DUAL;
TZ_OFFS
-------
-04:00
PostgreSQL tz_offset
PostgreSQL的时区信息可以从pg_timezone_names动态视图获取。
postgres=# select * from pg_timezone_names limit 10;
name | abbrev | utc_offset | is_dst
---------------------------+--------+------------+--------
PST8PDT | PDT | -07:00:00 | t
Eire | IST | 01:00:00 | t
Antarctica/DumontDUrville | DDUT | 10:00:00 | f
Antarctica/Syowa | SYOT | 03:00:00 | f
Antarctica/McMurdo | NZST | 12:00:00 | f
Antarctica/Rothera | ROTT | -03:00:00 | f
Antarctica/Casey | AWST | 08:00:00 | f
Antarctica/Davis | DAVT | 07:00:00 | f
Antarctica/Macquarie | MIST | 11:00:00 | f
Antarctica/South_Pole | NZST | 12:00:00 | f
(10 rows)
要实现与oracle tz_offset同类的功能,自定义一个函数即可。
postgres=# create or replace function tz_offset(text) returns text as $$
select utc_offset::text from pg_timezone_names where name=$1 limit 1;
$$ language sql strict;
CREATE FUNCTION
验证对应oracle的例子
postgres=# select tz_offset('US/Eastern');
tz_offset
-----------
-04:00:00
(1 row)
参考
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions202.htm