Oracle TIMESTAMP WITH TIMEZONE named zone vs offset


In oracle, is the named timezone always stored?

I have been testing this column within our system, and in some places the timestamp is shown as:

26-FEB-09 AM +13:00

but other times it's:

26-FEB-09 AM Pacific/Auckland

If the value is being stored as the former, does that mean the actual timezone is not being stored?

I worry because if a future date is stored with only an offset we might not be able to determine the actual time in the original timezone, because you can determine a offset from a timezone, but not vice versa.


Best Solution

It's pretty easy to test

 create table foo ( tswtz TIMESTAMP WITH TIME ZONE);

insert into foo values (TO_TIMESTAMP_TZ ('21-FEB-2009 18:00:00 -5:00', 'DD-MON-YYYY HH24:MI:SS TZH:TZM'));

insert into foo values (TO_TIMESTAMP_TZ ('21-FEB-2009 18:00:00 EST', 'DD-MON-YYYY HH24:MI:SS TZR'));
    select tswtz, extract(timezone_abbr from tswtz), extract(TIMEZONE_REGION from tswtz)
from foo;

------------- ------------------------------- ---------------------------------------------------------------- 
21-FEB-09 PM -05:00   UNK                          UNKNOWN                                                          
21-FEB-09 PM EST      EST                             EST                                                              

2 rows selected

It stores what you tell it. If you tell it an offset, that offset could be good for one or more timezones, so why would it just pick one?