Oracle 8.0.6 timezone/date format

[Bill McNAMARA_1]

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

[Wodisch_1]

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]

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 · Accepted solution]

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

[Printaporn_1]

HI Bill,
Even it was set in client with environment variable
NLS_DATE_FORMAT
ex
NLS_DATE_FORMAT=Mon DD YYYY HH24:MI:SS