RE: partitiong on timestamp with local timezone data type

A warning -- make sure that the clients and the database have the latest timezone files installed. Once you start using Local Timezone datatypes you don't want the data to be wrong. The cleanup job is one you do not want to do. Details are present as part of the patchset for updating the files.

As for the dbtimetimezone, I set it to GMT or 00:00. Oracle confirmed that it is ever so slightly faster since no offset needs to be calculated. So I went with that as a standard -- full disclosure, we don't used those data types. select dbtimezone from dual;
DBTIME

Joel Patterson
Database Administrator
904 928-2790

--

Joel Patterson
Sr. Database Administrator | Enterprise Integration Phone: 904-928-2790 | Fax: 904-733-4916
http://www.entint.com/

http://www.entint.com/

http://www.facebook.com/pages/Enterprise-Integration/212351215444231 http://twitter.com/#!/entint http://www.linkedin.com/company/18276?trk=tyah http://www.youtube.com/user/ValueofIT

This message (and any associated files) is intended only for the use of the addressee and may contain information that is confidential, subject to copyright or constitutes a trade secret. If you are not the intended recipient, you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message, is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and deleting it from your computer. Messages sent to and from us may be monitored. Any views or opinions presented are solely those of the author and do not necessarily represent those of the company. [v.1.1]

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Patterson, Joel Sent: Friday, May 24, 2013 9:32 AM
To: oracledbaquestions_at_gmail.com; ORACLE-L Subject: RE: partitiong on timestamp with local timezone data type

I believe only sysdate and systimestamp (I have to run, so spelling) relies on the OS clock.

Tom Kytes has some good discussion on data types in Expert Oracle database architecture. From the 9i/10g edition:

'...TIMESTAMP WITH LOCAL TIME ZONE... Upon modification in the database, the TIME ZONE supplied with the data is consulted, and the date/time component is normalized to the database time zone. So, if you were to insert a date/time using the time zone U.S./Pacific and the database time zone was U.S./Eastern, the final date/time information would be converted to the Eastern time zone and stored as a TIMESTAMP would be. Upon retrieval, the TIMESTAMP stored in the database would be converted to the time in the session's time zone.'

Joel Patterson
Database Administrator
904 928-2790

--

Joel Patterson
Sr. Database Administrator | Enterprise Integration Phone: 904-928-2790 | Fax: 904-733-4916
http://www.entint.com/

http://www.entint.com/

http://www.facebook.com/pages/Enterprise-Integration/212351215444231 http://twitter.com/#!/entint http://www.linkedin.com/company/18276?trk=tyah http://www.youtube.com/user/ValueofIT

This message (and any associated files) is intended only for the use of the addressee and may contain information that is confidential, subject to copyright or constitutes a trade secret. If you are not the intended recipient, you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message, is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and deleting it from your computer. Messages sent to and from us may be monitored. Any views or opinions presented are solely those of the author and do not necessarily represent those of the company. [v.1.1]

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dba DBA Sent: Thursday, May 23, 2013 1:45 PM
To: ORACLE-L
Subject: partitiong on timestamp with local timezone data type

Oracle 11.2.0.3
Production OS: Solaris (don't know the version) We insert date data that comes in a variety of timezones. We can tell what timezone each record is in. We need to normalize this date field to the timezone of our DB. We partition on this field. Timestamp with Local Timezone appears to meet this requirement and normalize it for me as long as I state the local timezone when I insert and set the dbtimezone parameter.

Concern (I am going to test this, but I want to make sure my test case is set up correctly).
-- what happen sif the Timezone of the OS is out of sync with what we have set in dbtimezone?

• my understanding is that Oracle figures out what partition to put date data into base don the system clock which comes from an OS call.
• The production team is from another company and I have no contact with them. So mistakes can happen.
• We partition by hour and we absolutely have to get data out of our system based on a specific number of hours. If data is here 1 hour late, it is a big problem.

My concern may be totally off base because I have not worked with this data type yet.

I am planning on running the following tests. 1. create table, partition by range on timestamp with local timezone by hour. insert records in a variety of different timezones. Verify this is correct. 2. change the OS clock without changing the dbtimezone parameter. 3. see what the data looks like when I query it.

Test 2:
with the OS time out of sync with the DB timezone 1. insert records to the table 2. see what they look like. 3. figure out what partition they are in.

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri May 24 2013 - 15:54:13 CEST