Timestamp with time zone

One of the databases I support is based in America. The application code makes extensive use of the TIMESTAMP WITH TIME ZONE data type to ensure that the date/time fields are correctly displayed according to the time zone. Lets try querying on using the TIMESTAMP WITH LOCAL TIME ZONE column.

Here's a simple example. I create a small table with 10,000 rows in Oracle 11.2.0.3.

 CREATE TABLE dates(  
  id NUMBER,  
  lt TIMESTAMP WITH LOCAL TIME ZONE  
 );

 INSERT /*+ APPEND */  
 INTO   dates  
 SELECT level,  
        TRUNC(SYSTIMESTAMP) + level  
 FROM   dual  
 CONNECT BY level <= 10000;  

 ALTER TABLE dates ADD CONSTRAINT pk_dates PRIMARY KEY (id);  
 CREATE INDEX i_dates_03 ON dates(lt);  
 exec dbms_stats.gather_table_stats(user, 'dates');  

The key thing to note is the index on the "lt" column. Lets run a simple query against the table, using the TO_TIMESTAMP function.

 SELECT *  
 FROM   dates  
 WHERE  lt = TO_TIMESTAMP('28/02/2013 00:00:00','dd/mm/yyyy hh24:mi:ss');

 --------------------------------------------------  
 | Id | Operation                  | Name         |  
 --------------------------------------------------  
 |  0 | SELECT STATEMENT           |              |  
 |  1 | TABLE ACCESS BY INDEX ROWID| DATES        |  
 |* 2 |  INDEX RANGE SCAN          | I_DATES_03   |  
 --------------------------------------------------  
 Predicate Information (identified by operation id):  
 ---------------------------------------------------  
   2 - access("LT"=TIMESTAMP' 2013-02-28 00:00:00.000000000')  

Note that an the index is being used to access the table. The access predicate on step 2 confirms this.

What if a TO_TIMESTAMP_TZ function is applied? I thought that this should also use the index since we are applying a timezone/timestamp comparison to a field stored as TIMESTAMP WITH LOCAL TIME ZONE.

 SELECT *
 FROM   dates
 WHERE  lt = TO_TIMESTAMP_TZ('28/02/2013 00:00:00 0:00','dd/mm/yyyy hh24:mi:ss tzh:tzm');

 -----------------------------------  
 | Id | Operation        | Name    |  
 -----------------------------------  
 |  0 | SELECT STATEMENT |         |  
 |* 1 | TABLE ACCESS FULL| DATES   |  
 -----------------------------------  
 Predicate Information (identified by operation id):  
 ---------------------------------------------------  
   1 - filter(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("LT"))=  
        SYS_EXTRACT_UTC(TO_TIMESTAMP_TZ('28/02/2013 00:00:00 0:00','dd/mm/yyyy hh24:mi:ss tzh:tzm')))  

This is what surprised me - the index is not being used. Instead, a full table scan and an INTERNAL_FUNCTION is used (which would have decreased the speed of my update operation had I not noticed it).

What I decided to do was use an alter session command so my session was in the correct time zone:

 ALTER SESSION SET TIME_ZONE='-5:00';  

As well as this I converted my TO_TIMESTAMP_TZ function calls to be TO_DATE.