Just a simple question about the "AT TIME ZONE" function in Oracle. It seems the time zone named CEST is not a valid argument for this function despite the fact it is listed in the view V$TIMEZONE_NAMES
select * from V$TIMEZONE_NAMES where tzname like '%Rome%';
TZNAME TZABBREV CON_ID
----------- -------- ------
Europe/Rome LMT 0
Europe/Rome RMT 0
Europe/Rome CET 0
Europe/Rome CEST 0
SQL>
When I try to convert a timestamp to the time zone name CET, everything works as expected
SELECT TO_TIMESTAMP('2012-MAG-24 02:00:00','RRRR-MON-DD HH24:MI:SS')
AT TIME ZONE 'CET' "CET"
FROM DUAL;
CET
--------------------------------
24-MAG-12 02:00:00,000000000 CET
SQL>
When I change the time zone to CEST, I get this error:
SELECT TO_TIMESTAMP('2012-MAG-24 02:00:00','RRRR-MON-DD HH24:MI:SS')
AT TIME ZONE 'CEST' "CEST"
FROM DUAL;
ORA-01882: timezone region not found
SQL>
My question is: why is the CEST time zone NOT valid for this conversion?
For the sake of clarity I'm running an Oracle 12c instance
AT TIME ZONE
takes a time zone name - you're passing in the time zone abbreviation. The names are in the TZNAME
column of the results you're showing us, but you're using the value from TZABBREV
. Abbreviations are a bad idea because:
Basically, the answer is to use 'Europe/Rome'
instead of 'CET'
.
See more on this question at Stackoverflow