Database Reference
In-Depth Information
report it to the user or handle it in the application. If there is an error, it is usually not beneficial to
continue processing the data. There will be examples of this template in action later in our code.
A WORD ABOUT TIME ZONES
You'll notice that Listing 5-12 gets the current time of the Oracle Database with this call:
TO_CHAR( CURRENT_TIMESTAMP, 'DY MON DD HH24:MI:SS TZD YYYY' )
The goal is to match the time format with what Java on the local client gives us, just for presentation's
sake. The time right now (for me), from Java is:
Sat Dec 04 11:29:39 EST 2010
Notice the format string includes the element “TZD” which presents the
time zone
. My time zone is
EST
(eastern standard time). We use
CURRENT_TIMESTAMP
instead of
SYSDATE
in order to get the time zone.
Oracle timestamp data must be directed to retain time zone if you want that. Generally, Oracle database
assumes the time zone of the client for data generated during a session; however, often the time zone
provided by the session is some number of hours offset from Greenwich mean time (GMT). Living in the
southeast United States with daylight saving time means that our time zone is usually minus 5 hours from
GMT.
Note that to get completely identical results, you would have to uppercase the date
String
returned by
Java. When we compare the results to what is returned by Oracle database, we'll see that Oracle gives
names of the days of the week and names of the months in all uppercase. Looking ahead, we will see
these date and time representations:
Client date: Sat Dec 04 14:59:49 EST 2010
Server date: SAT DEC 04 14:59:50 EST 2010
You'll be interested to know (or just consider) that many cities, states, countries and continents share this
offset (minus 5 GMT). Therefore, knowing the offset doesn't guarantee that Oracle database can report the
time zone in TZD format; although, the offset can be reported.
With some Oracle clients, like TOAD, the client does not inform the server what time zone name to use.
The server knows the offset, but not the name. Observe the output from these commands to observe the
phenomenon.
SELECT TO_CHAR( CURRENT_TIMESTAMP, 'DY MON DD HH24:MI:SS TZD YYYY' ) FROM DUAL;
SELECT * FROM sys.gv_$timezone_names
WHERE tzname LIKE 'America%' --AND tzabbrev = 'EST'
;
ALTER SESSION SET TIME_ZONE = 'America/New_York';
SELECT TO_CHAR( CURRENT_TIMESTAMP, 'DY MON DD HH24:MI:SS TZD YYYY' ) FROM DUAL;
After altering our session to specify our time zone name, Oracle database can correctly report
EST
or
whatever is your current time zone. Here's what I see as the results:
SAT DEC 04 11:57:49 2010
