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 11.36.25.390713 AM +13:00

but other times it's:

26-FEB-09 11.36.25.390713 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.

Thanks

From stackoverflow

• 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;
  
  
  TSWTZ         EXTRACT(TIMEZONE_ABBRFROMTSWTZ) EXTRACT(TIMEZONE_REGIONFROMTSWTZ)                                
  ------------- ------------------------------- ---------------------------------------------------------------- 
  21-FEB-09 06.00.00.000000000 PM -05:00   UNK                          UNKNOWN                                                          
  21-FEB-09 06.00.00.000000000 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?

  half_brick : OK, cool, that's what I've expected. So say I have a "created" column which I want to have a default value of the current timestamp. Currently in my DB a call to systimestamp returns a timestamp with an offset. How do I change this to use the named timezone? Thanks

  half_brick : select to_char(systimestamp, 'YYYYMMDD HH24:MI:SS.FF TZR') || ' ' || SESSIONTIMEZONE from dual; Results in: 20090226 13:34:05.481439 +13:00 +11:00 Why is the SESSIONTIMEZONE 11? And also how do I make the TZR format return the named timezone. Cheers

• I've found that setting the TimeZone and format within ODP.NET when a connection is opened seems to solve this problem:

  OracleGlobalization info = conn.GetSessionInfo();
  info.TimeZone = "Pacific/Auckland";
  info.TimeStampFormat = "DD-MON-YYYY HH:MI:SS.FF AM";
  info.TimeStampTZFormat = "DD-MON-YYYY HH:MI:SS.FF AM TZR";
  conn.SetSessionInfo(info);