- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Oracle 8.0.6 timezone/date format
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-26-2002 08:46 AM
тАО12-26-2002 08:46 AM
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-26-2002 03:58 PM
тАО12-26-2002 03:58 PM
Re: Oracle 8.0.6 timezone/date format
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-26-2002 06:39 PM
тАО12-26-2002 06:39 PM
Re: Oracle 8.0.6 timezone/date format
Brian
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-26-2002 09:56 PM
тАО12-26-2002 09:56 PM
SolutionHi 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-27-2002 03:19 AM
тАО12-27-2002 03:19 AM
Re: Oracle 8.0.6 timezone/date format
Even it was set in client with environment variable
NLS_DATE_FORMAT
ex
NLS_DATE_FORMAT=Mon DD YYYY HH24:MI:SS