Oracle timestamp with timezone TZR/TZD handling

The documentation you linked to says this about the TZR element:

Time zone region information. The value must be one of the time zone region names supported in the database. Valid in timestamp and interval formats, but not in DATE formats.

PDT is a supported abbreviation, but not a name. PST is both:

select tzabbrev, count(*)
from v$timezone_names
where tzabbrev in ('PST', 'PDT')
group by tzabbrev;

select *
from v$timezone_names
where tzname in ('PST', 'PDT');

fiddle

So your query is failing because PDT is not recognised as a valid value for TZR, as the error kind of says.

You said "TZR handles some three-letter codes but not others", which is true, but because only some codes are listed as region names as well as abbreviations.

In general, abbreviations are not unique - often they all relate to the same actual region, but not always; CST count be America/Chicago or Asia/Shanghai. And even when they do kind of represent the same time, the DST behaviour might be different. For example CET is the abbreviation for Africa/Algiers and Europe/Amsterdam; the latter switches to CEST, the former does not.

For the TZD element it says (emphasis added):

Daylight saving information. The TZD value is an abbreviated time zone string with daylight saving information. It must correspond with the region specified in TZR. Valid in timestamp and interval formats, but not in DATE formats.

So it usually doesn't make sense to use it on its own, at least when converting from a string to a timestamp. If you only supply TZD then it will try to apply it using your session time zone - if that is a region not an offset - and will error if the combination is not valid. So if your session time zone is America/Los_Angeles then it will accept PDT for TZD, but it won't for Europe/London etc. - fiddle.

And if you're supplying TZR as a region name (explicitly or implicitly) then DST will be inferred anyway, so with both it's perhaps just another cross-check, since it will check the time is valid for the declared DST - fiddle.

When converting from a timestamp to a string it can be used alone to get appropriate time zone-aware abbreviation; but it can't be used to convert the other way, unless you are always already in the matching time zone region.

Oracle uses a time zone file to specify details of the time zones and when DST occurs.

The view V$TIMEZONE_NAMES gives details about the current time zone information available to the database:

For example:

SELECT *
FROM   V$TIMEZONE_NAMES
WHERE  TZABBREV IN ('PST', 'PDT')
FETCH FIRST 10 ROWS ONLY;

May output:

Which shows that Oracle database can, if provided with up-to-date time zone files, support the PDT time zone.

If you are finding that PDT is not supported then that is because the time zone file that the database is using does not include details of that time zone.

You can find details of the current time zone file using:

SELECT * FROM v$timezone_file;

Which may output:

You can also find details of the version using:

SELECT tz_version FROM registry$database;

or:

SELECT property_name, property_value
FROM   database_properties
WHERE  property_name LIKE 'DST_%'
ORDER BY property_name;

Which may output:

If you need to, you can then upgrade the database time zone file.

fiddle