Преобразование TIMESTAMP WITH TIME ZONE в Oracle 12c

#sql #oracle #timezone

По каким-то причинам не получается правильно преобразовать TIMESTAMP WITH TIMEZONE
из одной временной зоны в другую, пример:

SELECT SYSTIMESTAMP,
       DBTIMEZONE,
       CURRENT_TIMESTAMP
       SESSIONTIMEZONE
  FROM DUAL;




=============================================================================================================================================================================================================
|                   SYSTIMESTAMP                   |                    DBTIMEZONE
                   |                CURRENT_TIMESTAMP                 |           
     SESSIONTIMEZONE                  |
=============================================================================================================================================================================================================
|            20.07.2017 7:15:33 -04:00             |                  Europe/Moscow
                  |            20.07.2017 14:15:33 +03:00            |            
         +03:00                      |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------




Второй пример:

SELECT 
  SYSTIMESTAMP,
  SYSTIMESTAMP AT TIME ZONE 'Europe/Moscow' Moscow,
  SYSTIMESTAMP AT TIME ZONE 'America/New_York' New_York
  FROM DUAL




==========================================================================================================================================================
|                   SYSTIMESTAMP                   |                      MOSCOW
                     |                     NEW_YORK                     |
==========================================================================================================================================================
|            20.07.2017 7:09:25 -04:00             |            20.07.2017 11:09:25
+00:00            |            20.07.2017 11:09:25 +00:00            |
----------------------------------------------------------------------------------------------------------------------------------------------------------


В данном случае в результате преобразования часовой пояс вообще сбросился, не смотря
на успешное выполнение преобразования.


Почему в первом примере результат CURRENT_TIMESTAMP отстает на час от реального,
которое должно быть равно 15:15:33, а не 14:15:33?
Почему во втором примере, не смотря на успешное выполнение команды, конвертация не
происходит? Может какие-то параметры базы данных не настроены, или я не понимаю как
AT TIME ZONE работает? Наименования временных зон я брал из V$TIMEZONE_NAMES.


Версия БД: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

UPD:
Данные из nls_session_parameters

SQL Developer

NLS_LANGUAGE    RUSSIAN
NLS_TERRITORY   RUSSIA
NLS_CURRENCY    ₽
NLS_ISO_CURRENCY    RUSSIA
NLS_NUMERIC_CHARACTERS  , 
NLS_CALENDAR    GREGORIAN
NLS_DATE_FORMAT DD.MM.RR
NLS_DATE_LANGUAGE   RUSSIAN
NLS_SORT    RUSSIAN
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT    DD.MM.RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT  HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY   ₽
NLS_COMP    BINARY
NLS_LENGTH_SEMANTICS    BYTE
NLS_NCHAR_CONV_EXCP FALSE


dbForge

NLS_LANGUAGE    AMERICAN
NLS_TERRITORY   AMERICA
NLS_CURRENCY    $
NLS_ISO_CURRENCY    AMERICA
NLS_NUMERIC_CHARACTERS  .,
NLS_CALENDAR    GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE   AMERICAN
NLS_SORT    BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT  HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY   $
NLS_COMP    BINARY
NLS_LENGTH_SEMANTICS    CHAR
NLS_NCHAR_CONV_EXCP FALSE

    

Ответы

Ответ 1

К 1-ой части вопроса

SYSTIMESTAMP возвращает текущее время и часовой пояс операционной системы машины
на которой установлен сервер БД.  

CURRENT_TIMESTAMP возвращает эти же данные учитывая часовой пояс сессии на клиенте,
который определяется клиентом из окружения (установки региона на ОС, переменные) в
котором он запущен и может быть изменён непосредственно: setenv ORA_SDTZ="Europe/Moscow"
или alter session set time_zone='+03:00'. 

Посмотреть часовой пояс сессии можно так: 

select sessiontimezone from dual;


В примере разница между Москвой и Нью-Йорком составляет -4 и +3 = 7 часов, что соответствует
действительности. Значит время, которое возвращает SYSTIMESTAMP тоже с отставанием,
т.е. системное время установленно не верно.

Ко 2-ой части вопроса

Преобразование даты и времени производится на стороне клиента и зависит от его NLS
настроек, часового пояса (см. выше), которые можно посмотреть: 

select * from nls_session_parameters;


Некоторые клиенты, как в данном примере с dbForge\IDEA "потерялся" часовой пояс,
или sqlplus не учитывает изменения часового пояса в России с октября 2014 года,   выполняют
преобразование не всегда верно. Поэтому при сомнении имеет смысл выполнить запрос на
различных клиентах.

На заметку не в рамках вопроса

Oracle рекомендует устанавливать DBTIMEZONE на UTC, если не используются по каким-то
специфическим соображениям тип данных TIMESTAMP WITH LOCAL TIME ZONE.