Oracle New_time函数无法转换日期列的dst更改

嗨，

我有2个表列，日期为数据类型。1家商店在中部标准时间，其他在中部当地时间。每天都有一个批运行来将这些值填充到另一个oracle数据库。目标db列在东部本地。当我使用new_time函数将central standard和central local转换为东部local时，dst的变化不会反映出来。


new_time只是为cst时间增加了2个小时，如下图所示。


选择new_time(to_date('01-JAN-2018 22:00:00 '，'dd-MON-YYYY HH24:MI:SS')，'CST'，'EDT')，
新时间 (to_date ('01-jun-2018 22:00:00 '，'dd-MON-YYYY HH24:MI:SS')，'CST'，'EDT') 来自双重;



帮我把中央标准和中央地方转换成东部地方。

检查以下MOS注释:

DST对NEW_TIME函数没有影响 (Doc ID 419404.1)

根据该说明:

NEW_TIME limitations - no automatic DST changes
The most important thing to realize when using the new_time function is that all the above mentioned possible time zones are completely static. For example EST always means "GMT-5", EDT always means "GMT-4", PST always means "GMT-8", etc. etc. Therefore any program using the new_time function must itself have knowledge about the start- and end-dates of DST, in order to do a correct conversion to the correct time for the time of year. The new_time function never has had, and never will have, automatic DST switchover between time zones. Essentially the new_time function is simply a function that subtracts or adds a number of hours from a DATE, where the names that can be used are simply a alias for a fixed number of hours.

所以你可以显式地处理它，例如

SQL> alter session set nls_timestamp_tz_format = 'DD-MON-YYYY HH24:MI:SS TZR';

Session altered.

SQL> select to_timestamp_tz('05/05/2006 14:00:00 GMT', 'MM/DD/YYYY HH24:MI:SS TZR') at time zone 'US/Eastern' from dual;

TO_TIMESTAMP_TZ('05/05/200614:00:00GMT','MM/DD/YYYYHH24:MI:SSTZR')ATTIMEZON
---------------------------------------------------------------------------
05-MAY-2006 10:00:00 US/EASTERN

SQL> select to_timestamp_tz('01/01/2006 14:00:00 GMT', 'MM/DD/YYYY HH24:MI:SS TZR') at time zone 'US/Eastern' from dual;

TO_TIMESTAMP_TZ('01/01/200614:00:00GMT','MM/DD/YYYYHH24:MI:SSTZR')ATTIMEZON
---------------------------------------------------------------------------
01-JAN-2006 09:00:00 US/EASTERN

或者你可以写一个包装来模仿new_time，但是用dst处理

create or replace function new_time_dst(in_date date, tz1 varchar2, tz2 varchar2) return date deterministic as
begin   
  return to_date(
           to_char(
             to_timestamp_tz(
               to_char(in_date,'YYYYMMDDHH24:MI:SS')||' '||trim(tz1)
               ,'YYYYMMDDHH24:MI:SS TZR') at time zone tz2
           ,'YYYYMMDDHH24:MI:SS')
        ,'YYYYMMDDHH24:MI:SS');
end;
/

SQL> SELECT NEW_TIME_DST(TO_DATE('05/05/2006 14:00:00', 'MM/DD/YYYY HH24:MI:SS'),'GMT','US/EASTERN') from dual;

NEW_TIME_DST(TO_DATE
--------------------
05-MAY-2006 10:00:00

SQL> SELECT NEW_TIME_DST(TO_DATE('01/01/2006 14:00:00', 'MM/DD/YYYY HH24:MI:SS'),'GMT','US/EASTERN') from dual;

NEW_TIME_DST(TO_DATE
--------------------
01-JAN-2006 09:00:00

附录: 感谢Roderick Manalac指出这一点。您需要记住，只有在特定区域的时区定义不变的情况下，这种情况下的确定性才有效。在世界的某些地方，他们改变了是否 * 有 * 夏令时，或者什么时候开始和停止。你需要小心这里。