Invalid time zone region while calling AT TIME ZONE function in Oracle database

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

Jon Skeet
people
quotationmark

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:

  • They're not unique, so can't act as identifiers.
  • They only apply for part of the time... so in modern times, Europe/Rome varies between CET and CEST, as you can see from the table. If you've got to work out which abbreviation is actually in effect at a particular time, you might as well just specify the offset...

Basically, the answer is to use 'Europe/Rome' instead of 'CET'.

people

See more on this question at Stackoverflow