Re: Timezone Conversion

From: Phil <philip.moore_at_hp.com>
Date: 7 Jul 2005 10:42:30 -0700
Message-ID: <1120758150.078104.113800@g44g2000cwa.googlegroups.com>

Hi,

Try using the Oracle built-in datatype:

TIMESTAMP WITH LOCAL TIME ZONE Here is the quote from the SQL Reference (9iR2): "231 TIMESTAMP
( fractional_
seconds_precision)
WITH LOCAL TIME
ZONE
All values of TIMESTAMP WITH TIME ZONE, with the following exceptions:
Data is normalized to the database time zone when it is stored in the database.
When the data is retrieved, users see the data in the session time zone."

It will require a little coding change in your app - such as replacing any "SYSDATE" references to "SYSTIMESTAMP".

Here is an example (I'm in the "EST" timezone):

---

philip_at_ORA9iR2 -> DROP TABLE time_test
  2 /

Table dropped.

philip_at_ORA9iR2 ->
philip_at_ORA9iR2 -> CREATE TABLE time_test

  2  ( time_with_local_tz TIMESTAMP WITH LOCAL TIME ZONE
  3  , time_with_tz TIMESTAMP WITH TIME ZONE
  4  , time_without_tz DATE

  5 )
  6 /

Table created.

philip_at_ORA9iR2 ->
philip_at_ORA9iR2 -> INSERT INTO time_test
  2 VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSDATE)   3 /

1 row created.

philip_at_ORA9iR2 ->
philip_at_ORA9iR2 -> SELECT * FROM time_test   2 /

TIME_WITH_LOCAL_TZ

---

TIME_WITH_TZ

---

TIME_WITHOU

---

07-JUL-05 10.40.30.496852 AM
07-JUL-05 01.40.30.496852 PM -04:00
07-JUL-2005

philip_at_ORA9iR2 ->
philip_at_ORA9iR2 -> ALTER SESSION SET TIME_ZONE = 'PST'   2 /

Session altered.

philip_at_ORA9iR2 ->
philip_at_ORA9iR2 -> SELECT * FROM time_test   2 /

TIME_WITH_LOCAL_TZ

---

TIME_WITH_TZ

---

TIME_WITHOU

---

07-JUL-05 10.40.30.496852 AM
07-JUL-05 01.40.30.496852 PM -04:00
07-JUL-2005



-------------------------------------------------------

Received on Thu Jul 07 2005 - 12:42:30 CDT