RE: partitiong on timestamp with local timezone data type

  • From: "Patterson, Joel" <jpatterson@xxxxxxxxxx>
  • To: "oracledbaquestions@xxxxxxxxx" <oracledbaquestions@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 24 May 2013 09:31:32 -0400

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!/entint

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Dba DBA
Sent: Thursday, May 23, 2013 1:45 PM
Subject: partitiong on timestamp with local timezone data type

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 
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 

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.



Other related posts: