Какова обратная функция SYS_EXTRACT_UTC() в Oracle?

Question

Какова обратная функция SYS_EXTRACT_UTC() в Oracle?

Вопрос

Позволять original_date быть date, Как я могу вернуть его значение из результата SYS_EXTRACT_UTC(cast(original_date as timestamp ))исключительно внутренними функциями Oracle, т.е. независимо от установки часового пояса в базе данных.

Фон

Некоторая глупая программа записывает значения даты в одну из таблиц в UTC, то есть

cast(SYS_EXTRACT_UTC(SYSTIMESTAMP) as date)

хранится вместо SYSDATE в столбце типа даты этой таблицы.

Во всех других таблицах просто SYSDATE хранится в таких столбцах. Моя задача - использовать эти значения вместе, поэтому я хочу вернуть эффект SYS_EXTRACT_UTC(). Я могу решить это, только если я укажу вручную свой часовой пояс, т.е.

cast( FROM_TZ(cast(my_utc_date as TIMESTAMP), 'UTC')  AT TIME ZONE 'Europe/Budapest' as date)

Но если я использую DBTIMEZONE вместо 'Europe/Budapest', тогда я получаю неправильный результат, вероятно, потому что летнее время игнорируется DBTIMEZONE

Например, когда to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS') = '2016-05-19 13:45:12', программа сохраняет

 cast(SYS_EXTRACT_UTC(cast(SYSDATE as timestamp)) as date)

Мой тестовый запрос:

SELECT  
        original_date,
        stored_utc_date,
        cast( FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC')  AT TIME ZONE 'Europe/Budapest' as date) as reverted_good,
        cast( FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC')  AT TIME ZONE DBTIMEZONE as date) as reverted_wrong
from (
        select original_date, cast( SYS_EXTRACT_UTC(cast(original_date as timestamp )) as date) stored_utc_date 
        from (select to_date('2016-05-19 13:45:12','YYYY-MM-DD HH24:MI:SS') original_date from dual)
    )

и его результат:

ORIGINAL_DATE       STORED_UTC_DATE     REVERTED_GOOD       REVERTED_WRONG    
------------------- ------------------- ------------------- -------------------
2016-05-19 13:45:12 2016-05-19 11:45:12 2016-05-19 13:45:12 2016-05-19 12:45:12

3

oracle date timezone utc date-conversion

Источник

user4924819 19 май '16 в 12:49

1 ответ

Решение

Другие вопросы по тегам oracle date timezone utc date-conversion

user266304 19 май '16 в 14:18 2016-05-19 14:18 · Accepted Answer · 2016-05-19 14:18

Вы можете получить область часового пояса systimestamp и использовать это:

FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE to_char(systimestamp, 'TZR')

С вашими данными испытаний (но меняя Будапешт на Лондон, так как это мой регион):

SELECT  
        original_date,
        stored_utc_date,
        cast(FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/London' as date) as reverted_good,
        cast(FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE DBTIMEZONE as date) as reverted_wrong,
        cast(FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE TO_CHAR(systimestamp, 'TZR') as date) as reverted_right
from (
        select original_date, cast( SYS_EXTRACT_UTC(cast(original_date as timestamp )) as date) stored_utc_date 
        from (select to_date('2016-05-19 13:45:12','YYYY-MM-DD HH24:MI:SS') original_date from dual)
    )
/

ORIGINAL_DATE       STORED_UTC_DATE     REVERTED_GOOD       REVERTED_WRONG      REVERTED_RIGHT    
------------------- ------------------- ------------------- ------------------- -------------------
2016-05-19 13:45:12 2016-05-19 12:45:12 2016-05-19 13:45:12 2016-05-19 12:45:12 2016-05-19 13:45:12

Кроме... это не работает все время, потому что TZR сообщается как смещение (так как оно основано на операционной системе TZ), и вы не можете угадать регион из смещения. Если первоначальная дата была зимой, а вы запускаете ее летом или наоборот, то возвращенная дата будет через час. Таким образом, половина возвращенных дат всегда будет неправильной, но какая половина будет зависеть от того, когда вы выполните запрос.

Похоже, что вы можете обойти это, используя DBTIMEZONE в качестве местного часового пояса:

cast(FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE DBTIMEZONE
  as timestamp with local time zone

Ваш тестовый запрос снова:

SELECT  
        original_date,
        stored_utc_date,
        cast(FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/London' as date) as reverted_good,
        cast(FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE DBTIMEZONE as date) as reverted_wrong,
        cast(cast(FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE DBTIMEZONE as timestamp with local time zone) as date) as reverted_right
from (
        select original_date, cast( SYS_EXTRACT_UTC(cast(original_date as timestamp )) as date) stored_utc_date 
        from (select to_date('2016-05-19 13:45:12','YYYY-MM-DD HH24:MI:SS') original_date from dual)
    )
/

ORIGINAL_DATE       STORED_UTC_DATE     REVERTED_GOOD       REVERTED_WRONG      REVERTED_RIGHT    
------------------- ------------------- ------------------- ------------------- -------------------
2016-05-19 13:45:12 2016-05-19 12:45:12 2016-05-19 13:45:12 2016-05-19 12:45:12 2016-05-19 13:45:12

Более широкий тестовый запрос с датами в течение года:

with t as (
  select from_tz(cast(add_months(trunc(sysdate, 'MM'), 1-level) as timestamp), 'Europe/London')
    as original_systimestamp
  from dual
  connect by level <= 12
)
select original_systimestamp,
  cast(cast(from_tz(sys_extract_utc(original_systimestamp), 'UTC')
    at time zone dbtimezone as timestamp with local time zone) as date) as good_date,
  sys_extract_utc(original_systimestamp) as utc_timestamp,
  from_tz(sys_extract_utc(original_systimestamp), 'UTC')
    at time zone to_char(systimestamp, 'TZR') as at_systimezone,
  from_tz(sys_extract_utc(original_systimestamp), 'UTC')
    at time zone dbtimezone as at_dbtimezone,
  cast(from_tz(sys_extract_utc(original_systimestamp), 'UTC')
    at time zone dbtimezone as timestamp with local time zone) as at_local_dbtimezone
from t
order by original_systimestamp;

ORIGINAL_SYSTIMESTAMP               GOOD_DATE           UTC_TIMESTAMP         AT_SYSTIMEZONE               AT_DBTIMEZONE                AT_LOCAL_DBTIMEZONE        
----------------------------------- ------------------- --------------------- ---------------------------- ---------------------------- ----------------------------
2015-06-01 00:00:00.0 Europe/London 2015-06-01 00:00:00 2015-05-31 23:00:00.0 2015-06-01 00:00:00.0 +01:00 2015-05-31 23:00:00.0 +00:00 2015-06-01 00:00:00.0       
2015-07-01 00:00:00.0 Europe/London 2015-07-01 00:00:00 2015-06-30 23:00:00.0 2015-07-01 00:00:00.0 +01:00 2015-06-30 23:00:00.0 +00:00 2015-07-01 00:00:00.0       
2015-08-01 00:00:00.0 Europe/London 2015-08-01 00:00:00 2015-07-31 23:00:00.0 2015-08-01 00:00:00.0 +01:00 2015-07-31 23:00:00.0 +00:00 2015-08-01 00:00:00.0       
2015-09-01 00:00:00.0 Europe/London 2015-09-01 00:00:00 2015-08-31 23:00:00.0 2015-09-01 00:00:00.0 +01:00 2015-08-31 23:00:00.0 +00:00 2015-09-01 00:00:00.0       
2015-10-01 00:00:00.0 Europe/London 2015-10-01 00:00:00 2015-09-30 23:00:00.0 2015-10-01 00:00:00.0 +01:00 2015-09-30 23:00:00.0 +00:00 2015-10-01 00:00:00.0       
2015-11-01 00:00:00.0 Europe/London 2015-11-01 00:00:00 2015-11-01 00:00:00.0 2015-11-01 01:00:00.0 +01:00 2015-11-01 00:00:00.0 +00:00 2015-11-01 00:00:00.0       
2015-12-01 00:00:00.0 Europe/London 2015-12-01 00:00:00 2015-12-01 00:00:00.0 2015-12-01 01:00:00.0 +01:00 2015-12-01 00:00:00.0 +00:00 2015-12-01 00:00:00.0       
2016-01-01 00:00:00.0 Europe/London 2016-01-01 00:00:00 2016-01-01 00:00:00.0 2016-01-01 01:00:00.0 +01:00 2016-01-01 00:00:00.0 +00:00 2016-01-01 00:00:00.0       
2016-02-01 00:00:00.0 Europe/London 2016-02-01 00:00:00 2016-02-01 00:00:00.0 2016-02-01 01:00:00.0 +01:00 2016-02-01 00:00:00.0 +00:00 2016-02-01 00:00:00.0       
2016-03-01 00:00:00.0 Europe/London 2016-03-01 00:00:00 2016-03-01 00:00:00.0 2016-03-01 01:00:00.0 +01:00 2016-03-01 00:00:00.0 +00:00 2016-03-01 00:00:00.0       
2016-04-01 00:00:00.0 Europe/London 2016-04-01 00:00:00 2016-03-31 23:00:00.0 2016-04-01 00:00:00.0 +01:00 2016-03-31 23:00:00.0 +00:00 2016-04-01 00:00:00.0       
2016-05-01 00:00:00.0 Europe/London 2016-05-01 00:00:00 2016-04-30 23:00:00.0 2016-05-01 00:00:00.0 +01:00 2016-04-30 23:00:00.0 +00:00 2016-05-01 00:00:00.0

Но даже это работает, только если часовой пояс сеанса соответствует региону сервера базы данных; если я установлю часовой пояс сессии на что-то другое, чем Европа / Лондон, это будет далеко. И если вы полагаетесь на возможность установки часового пояса сеанса, первый запрос в вопросе с жестко заданным регионом на самом деле не намного хуже...

Также стоит отметить, что DBTIMEZONE не обязательно говорит вам что-нибудь полезное; Oracle рекомендует установить его в UTC. Так что, если вы не можете использовать это, и не можете использовать TZR, извлеченный из systimestamp (поскольку на самом деле это смещение, и вы не можете преобразовать это в регион, поэтому не можете знать информацию о летнем времени), я не думаю, что есть способ выяснить исходное системное время из всего, что хранится в базе данных. Кажется, что вам нужно указать регион часового пояса сервера в какой-то момент - либо установив часовой пояс сеанса, либо как в исходном запросе.