Skip to Main Content
  • Questions

  • Difference between database server time and database time zone

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, stephen.

Asked: March 25, 2012 - 12:42 pm UTC

Last updated: March 26, 2012 - 3:36 pm UTC

Version: 11.2

Viewed 10K+ times! This question is

You Asked

In a book, I read "Although the database server is in central daylight time, the database time zone is GMT". I am not able to understand this point. What is the practical use of it and what exactly is the use of DBTIMEZONE? I was thinking that DBTIMEZONE will be used for timestamp with local timezone datatype.

please excuse me if the question seems naive.

Regards,
Stephen.

and Tom said...

It is just saying that the database server - if you typed "date" on the command line - would report times relative to the time zone 'central', however in the database if you selected out a timestamp, it would be relative to GMT.


The point would be: you want your dates by default to be in GMT, for whatever reason. You want them in GMT.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Pls clarify

Stephen, March 26, 2012 - 2:44 pm UTC

Dear tom,
As per your answer I think that you meant that DBTIMEZONE decides how a timestamp column will be output.

select systimestamp from dual
output : 27-MAR-12 01.06.12.173000000 AM +05:30

select dbtimezone from dual
output : +00:00

select sysdate from dual
output : 2012-03-27 01:08:17

select current_timestamp from dual
output : 27-MAR-12 01.10.23.287000000 AM ASIA/CALCUTTA

But here even though the dbtimezone is +00:00 why systimestamp and current_timestamp not showing values according to dbtimezone? then what is the use of dbtimezone ?

Sorry this is confusing me ....

Thanks for your patience.

Regards,
Stephen.
Tom Kyte
March 26, 2012 - 3:35 pm UTC

....
Oracle Database normalizes all new TIMESTAMP WITH LOCAL TIME ZONE data to the time zone of the database when the data is stored on disk.Oracle Database does not automatically update existing data in the database to the new time zone. Therefore, you cannot reset the database time zone if there is any TIMESTAMP WITH LOCAL TIME ZONE data in the database. You must first delete or export the TIMESTAMP WITH LOCAL TIME ZONE data and then reset the database time zone. For this reason, Oracle does not encourage you to change the time zone of a database that contains data.

...........

it controls how things with local time zones are physically stored on disk.


http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_1004.htm#i2197908

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_5004.htm#i2082011


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.