Oracle TIMESTAMP WITH TIMEZONE named zone vs offse

问题:

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

回答1:

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?

回答2:

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);