ORA-01882: timezone region not found

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This is one of those funny messages where the lucky user is invited to contact Oracle support.
I don't know exactly how many calls Oracle is receiving reporting this, but in most cases it doesn't really make sense.
Anyway, here is an example, run on Oracle XE (10.2):

SELECT to_timestamp_tz('Fri, 28 Aug 2009 10:16:58 PDT',
'Dy, DD Mon YYYY HH24:MI:SS TZR') AT LOCAL as t
FROM DUAL;

ORA-01882: timezone region  not found

*Cause:    The specified region name was not found.
*Action:   Please contact Oracle Customer Support.

Now, as per oracle documentation PDT is not a time zone region, it's a daylight savings specifier.
Unfortunately you may easily get data in this format from an external data provider.
The problem is in that until you play inside the boundaries of your company, you can "easily" amend the input data, but when the data comes from outside is not so easy to convince the others that they are wrong, in the end they might be working with other partners who do not complain about the problem you report, so why bother?

To add more fun to the situation, consider the following query, bearing in mind that Oracle accepts TZD as timestamp format mask element to deal with daylight savings specifiers:

SELECT to_timestamp_tz('Fri, 28 Aug 2009 10:16:58 PDT',
'Dy, DD Mon YYYY HH24:MI:SS TZD') AT LOCAL as t
FROM DUAL;

T
--------------------------------------
28-AUG-09 10:16:58.000000000 AM +02:00

As soon as i removed the time zone region specifier, the query runs normally.
Now, i am no expert of time zones, but the result of this query doesn't look very good, even if i am open for discussion on this aspect.
I specified AT LOCAL, meaning to convert the timestamp value into the local session time zone, that is "+02:00" (use functions CURRENT_TIMESTAMP to retrieve the current time in the local time zone or SESSIONTIMEZONE to retrieve the time zone value only).

As i am in central europe time zone, I'd rather expect to get something like:

T
--------------------------------------
28-AUG-09 07:16:58.000000000 PM +02:00

If you remove AT LOCAL from the query, you get the same result.
I argue this is a consistent result.
Clearly one may object that since i (the others...) didn't specify the corresponding time zone region, the daylight savings specifier was ignored or interpreted in the wrong way.

According to the documentation TZD should always come with TZR and now i understand why:
there is a risk of misinterpreting the source time if i convert it into the local time zone.

In conclusion, if you are dealing with timestamps generated from external data sources, watch out for the correct time zone region format because PDT, for Oracle, doesn't seem to be a valid value.

May the root cause of the problem reside in a view called V$TIMEZONE_NAMES?

This view contains both the time zone name and its abbreviation.
Whereas time zone region PST (TZNAME column in the view) has got a corresponding abbreviation (TZABBREV) with the same value, PDT shows up only as an abbreviation of multiple time zone regions, without a corresponding value, indeed you may see it associated to PST for instance.

So, how did i work around the problem?
I try replacing PDT with PST PDT whenever i encounter ORA-01882.
Horrible practice, but i could not find a better one.

See message translations for ORA-01882 and search additional resources.