Geek Answers Handbook

Oracle TIMESTAMP with TIMEZONE named zone vs offset

Does the oracle always keep the named time zone?

I tested this column inside our system, and in some places the timestamp is displayed as:

26-FEB-09 11.36.25.390713 AM +13:00

but in other cases it is:

 26-FEB-09 11.36.25.390713 AM Pacific/Auckland

If the value is stored as the first, does this mean that the actual time zone is not saved?

I am worried because if the future date is stored only with an offset, we will not be able to determine the actual time in the original time zone, because you can determine the offset from the time zone, but not vice versa.

thanks

+6
timezone oracle datetime timestamp dst
source share
2 answers

I found that setting TimeZone and the format in ODP.NET when opening a connection 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);
+2
source share

It is quite easy to check.

  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

He keeps what you tell him. If you say this offset, this offset may be good for one or more time zones, so why just choose one?

+8
source share

More articles:


All Articles