Oracle TIMESTAMP WITH TIMEZONE named zone vs offset

oracle datetime timezone timestamp dst
tobyc picture tobyc · Feb 25, 2009 · Viewed 37.9k times · Source

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

Answer

Mark Brady picture Mark Brady · Feb 26, 2009

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?

Related questions

How to insert date values into table

How can I insert into table with different input using / ,with date datatype? insert into run(id,name,dob)values(&id,'&name',[what should I write here?]); I'm using oracle 10g.

Read More
Is java.sql.Timestamp timezone specific?

I have to store UTC dateTime in DB. I have converted the dateTime given in specific timezone to UTC. for that I followed the below code. My input dateTime is "20121225 10:00:00 Z" timezone is "Asia/Calcutta" My Server/DB(oracle) is …

Read More
In Oracle SQL: How do you insert the current date + time into a table?

I've written below code, but it only seems to insert the current date and not the current time. Anyone knows how to do that? insert into errortable (dateupdated,table1id) values (TO_DATE(sysdate, 'dd/mm/yyyy hh24:mi:ss'),1083);

Read More