Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle 8.0.6 timezone/date format

SOLVED
Go to solution
Bill McNAMARA_1
Honored Contributor

Oracle 8.0.6 timezone/date format

Hi all,

just wondering where oracle references it's date/timezone format from... and if anyone has an idea of why a date format returned from an sql statement would be forward of the system date? (apart from the obvious bad entry..)

Thanks,
Bill
It works for me (tm)
4 REPLIES
Wodisch_1
Honored Contributor

Re: Oracle 8.0.6 timezone/date format

Hi Bill,

maybe I don't understand your question, but you can configure the date format in a couple of different ways, like environment variables (UN*X), Windows locale settings, and the "session" you are connected to your oracle instance, maybe more...

Tell us a little more what you need, please.

Merry christmas,
Wodisch
Brian Crabtree
Honored Contributor

Re: Oracle 8.0.6 timezone/date format

Depends on where you are logging into the database from. A client connection through SQLNet will take the information from the TZ enviroment variable when the listener was started. A local connection will take the local TZ enviroment variable.

Brian

Yogeeraj_1
Honored Contributor
Solution

Re: Oracle 8.0.6 timezone/date format


Hi Bill,


Concerning timezones and oracle,

Note that in 9i there is a datatype "timestamp with timezone". The timestamp can store the date, time (down to 9 decimal places with seconds), and the TIMEZONE. The database "understands" timezones so if you get the difference between 1pm PST and 4pm EST it understands that it is in fact the same time. And so on.

In short, 9i supports and is timezone aware. 8i and before -- not so. You will have to write a piece of Java code to help you. (let me know it you want me to post an example)


It can be queried and altered as follows:
============================================
yd@ORA9I.WORLD> select dbtimezone from dual;

DBTIME
------
-04:00

yd@ORA9I.WORLD> alter database set time_zone = 'EST';

Database altered.

yd@ORA9I.WORLD> select dbtimezone from dual;

DBTIME
------
-04:00



yd@ORA9I.WORLD> connect / as sysdba;
Connected.
ops$tkyte@ORA9I.WORLD> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
ops$tkyte@ORA9I.WORLD> startup
ORACLE instance started.

Total System Global Area 358909124 bytes
Fixed Size 279748 bytes
Variable Size 335544320 bytes
Database Buffers 16777216 bytes
Redo Buffers 6307840 bytes
Database mounted.
Database opened.
yd@ORA9I.WORLD> select dbtimezone from dual;

DBTI
----
EST

============================================


Also, if you need to remember both the clients time AND the server time -- It is wiser to store 2 dates. That will be the best way to avoid all confusion. In that way you can query either column to get the correct representation of the date/time given the circumstances AND you'll be able to use indexes on the individual columns to speed up queries on dates if need be.

Oracle has a calendar/resource system built internally. Oracle has opted to store all dates in GMT in the database (it's used worldwide) and when needed, store the datetime in the timezone of the client as well. Oracle predominatly use the GMT date and convert it to the local timezone on the fly but on the occasions when needed -- it keeps the clients native date in a column as well.

Normally application development in environments where we have entries made and that are time zone sensitive, we use use an application context -- this will have an OFFSET that we can add to a DATE to convert from the server TZ to any TZ we want.


Hope this helps!

Best wishes for New year 2003

Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Printaporn_1
Esteemed Contributor

Re: Oracle 8.0.6 timezone/date format

HI Bill,
Even it was set in client with environment variable
NLS_DATE_FORMAT
ex
NLS_DATE_FORMAT=Mon DD YYYY HH24:MI:SS
enjoy any little thing in my life