五月 18, 2023
如何将一个时区的本地时间，转换为另一个时区的本地时间？
在 Oracle 中可以使用 NEW_TIME 这个函数实现这个目的。
Oracle NEW_TIME() 语法
NEW_TIME(date, timezone1, timezone2)
当 timezone1 中的日期和时间为日期时，NEW_TIME 返回时区 timezone2 中的日期和时间。在使用此功能之前，您需要将NLS_DATE_FORMAT参数设置为显示 24 小时制时间。无论日期的数据类型如何，返回类型始终为 DATE。
参数 timezone1 和 timezone2 可以是以下任何文本字符串：
- AST, ADT: Atlantic Standard or Daylight Time
- BST, BDT: Bering Standard or Daylight Time
- CST, CDT: Central Standard or Daylight Time
- EST, EDT: Eastern Standard or Daylight Time
- GMT: Greenwich Mean Time
- HST, HDT: Alaska-Hawaii Standard Time or Daylight Time.
- MST, MDT: Mountain Standard or Daylight Time
- NST: Newfoundland Standard Time
- PST, PDT: Pacific Standard or Daylight Time
- YST, YDT: Yukon Standard or Daylight Time
Oracle 示例
ALTER SESSION SET NLS_DATE_FORMAT =
'DD-MON-YYYY HH24:MI:SS';
SELECT NEW_TIME(TO_DATE(
'11-10-99 01:23:45', 'MM-DD-YY HH24:MI:SS'),
'AST', 'PST') "New Date and Time" FROM DUAL;
New Date and Time
--------------------
09-NOV-1999 21:23:45
PostgreSQL new_time
在 PostgreSQL 中，我们可以通过临时调整 timezone 配置参数，实现对应的 new_time 函数。
CREATE OR REPLACE FUNCTION new_time(ts timestamp without time zone, tz1 text, tz2 text)
RETURNS timestamp without time zone
STRICT
AS $$
declare
res timestamp;
begin
execute 'set local timezone to '''|| tz2||'''';
-- raise notice '%', current_setting('timezone');
select (timestamptz(ts || tz1))::timestamp into res;
return res;
end;
$$ LANGUAGE plpgsql;
PostgreSQL的时区列表。
postgres=# select * from pg_timezone_names;
name | abbrev | utc_offset | is_dst
----------------------------------+--------+------------+--------
Asia/Chongqing | CST | 08:00:00 | f
Asia/Macau | CST | 08:00:00 | f
Asia/Seoul | KST | 09:00:00 | f
Asia/Shanghai | CST | 08:00:00 | f
Asia/Singapore | +08 | 08:00:00 | f
Asia/Taipei | CST | 08:00:00 | f
Asia/Tokyo | JST | 09:00:00 | f
Asia/Urumqi | +06 | 06:00:00 | f
GMT | GMT | 00:00:00 | f
GMT+0 | GMT | 00:00:00 | f
GMT-0 | GMT | 00:00:00 | f
GMT0 | GMT | 00:00:00 | f
Hongkong | HKT | 08:00:00 | f
PRC | CST | 08:00:00 | f
UTC | UTC | 00:00:00 | f
......
(593 rows)
验证对应 Oracle 的例子：
PST -08:00 Pacific Standard Time AST -04:00 Atlantic Standard Time (Canada)
postgres=# select new_time('1999-10-11 01:23:45', '-04', '-08');
NOTICE: <-08>+08
new_time
---------------------
1999-10-10 21:23:45
(1 row)