Written by: ilmarkerm

Category: Blog entry

Published: July 17, 2026

Pre-vacation, friday evening, another temporal nugget for you.

EXTRACT from TIMESTAMP WITH TIME ZONE returns values in UTC. This is all documented, but I think quite unintuitive. Just have to keep it in mind.

SQL> with q as (select timestamp'2026-07-17 01:00:00+03:00' t from dual)
  2  select q.t, extract(day from q.t), extract(hour from q.t)
  3  from q;

T                                    EXTRACT(DAYFROMQ.T) EXTRACT(HOURFROMQ.T)
------------------------------------ ------------------- --------------------
2026-07-17 01.00.00,000000000 +03:00                  16                   22

Solution is to convert the value to TIMESTAMP before using EXTRACT.

SQL> with q as (select timestamp'2026-07-17 01:00:00+03:00' t from dual)
  2  select q.t, extract(day from cast(q.t as timestamp)) d, extract(hour from cast(q.t as timestamp)) h
  3  from q;

T                                             D          H
------------------------------------ ---------- ----------
2026-07-17 01.00.00,000000000 +03:00         17          1

Mandatory version information: 19.31

Written by: ilmarkerm

Category: Blog entry

Published: July 17, 2026

Covering indexes are indexes that have all columns present in the index, that the query needs. Removing the need to do an potentially very expensive TABLE ACCESS BY ROWID operation (that fetches the row information from table after locating the rows using an index).

But there is a little gotcha when using covering index on a column with TIMESTAMP WITH TIME ZONE data type.

Oracle silently indexes TIMESTAMP WITH TIME ZONE data type actually as a function SYS_EXTRACT_UTC(column), even if you do not specify the function excplcitly.

CREATE TABLE timestamptz_covering_test (
    v timestamp with time zone not null
);
INSERT INTO timestamptz_covering_test (v) SELECT systimestamp+numtodsinterval(rownum, 'second') from all_objects;
CREATE INDEX idx_timestamptz_covering_test_v on timestamptz_covering_test (v);

What Oracle actually indexed was

CREATE INDEX "IDX_TIMESTAMPTZ_COVERING_TEST_V" ON "TIMESTAMPTZ_COVERING_TEST" (SYS_EXTRACT_UTC("V"));

When using the index in WHERE clause, all works as expected, the index is used as an access predicate as expected.

select 1 from timestamptz_covering_test
where v between systimestamp and systimestamp+interval '1' minute;

----------------------------------------------------------------------
| Id  | Operation         | Name                            | E-Rows |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                                 |        |
|*  1 |  FILTER           |                                 |        |
|*  2 |   INDEX RANGE SCAN| IDX_TIMESTAMPTZ_COVERING_TEST_V |     62 |
----------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(SYS_EXTRACT_UTC(SYSTIMESTAMP(6)+INTERVAL'+00 00:01:00' 
              DAY(2) TO SECOND(0))>=SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))
   2 - access("TIMESTAMPTZ_COVERING_TEST"."SYS_NC00002$">=SYS_EXTRACT_UT
              C(SYSTIMESTAMP(6)) AND "TIMESTAMPTZ_COVERING_TEST"."SYS_NC00002$"<=SYS_E
              XTRACT_UTC(SYSTIMESTAMP(6)+INTERVAL'+00 00:01:00' DAY(2) TO SECOND(0)))

But… If we get into the covering index territory. Look at this example, we are NOT querying any extra columns, only the indexed v column, but suddenly we have TABLE ACCESS BY INDEX ROWID!!!

select v at time zone 'Europe/Tallinn' from timestamptz_covering_test
where v between systimestamp and systimestamp+interval '1' minute
 
Plan hash value: 3222247287
 
-----------------------------------------------------------------------------------------
| Id  | Operation                            | Name                            | E-Rows |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                                 |        |
|*  1 |  FILTER                              |                                 |        |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TIMESTAMPTZ_COVERING_TEST       |     62 |
|*  3 |    INDEX RANGE SCAN                  | IDX_TIMESTAMPTZ_COVERING_TEST_V |     62 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(SYS_EXTRACT_UTC(SYSTIMESTAMP(6)+INTERVAL'+00 00:01:00' DAY(2) TO 
              SECOND(0))>=SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))
   3 - access("TIMESTAMPTZ_COVERING_TEST"."SYS_NC00002$">=SYS_EXTRACT_UTC(SYSTIME
              STAMP(6)) AND "TIMESTAMPTZ_COVERING_TEST"."SYS_NC00002$"<=SYS_EXTRACT_UTC(SYSTIME
              STAMP(6)+INTERVAL'+00 00:01:00' DAY(2) TO SECOND(0)))

The reason is simple – SYS_EXTRACT_UTC returns TIMESTAMP data type – so in the index, all time zone information is lost and the original value has to be fetched from the table. In the query we should keep it in mind and rewrite.

select from_tz(sys_extract_utc(v), 'UTC') at time zone 'Europe/Tallinn' 
from timestamptz_covering_test
where v between systimestamp and systimestamp+interval '1' minute
 
Plan hash value: 4012349952
 
----------------------------------------------------------------------
| Id  | Operation         | Name                            | E-Rows |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                                 |        |
|*  1 |  FILTER           |                                 |        |
|*  2 |   INDEX RANGE SCAN| IDX_TIMESTAMPTZ_COVERING_TEST_V |     62 |
----------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(SYS_EXTRACT_UTC(SYSTIMESTAMP(6)+INTERVAL'+00 00:01:00' 
              DAY(2) TO SECOND(0))>=SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))
   2 - access("TIMESTAMPTZ_COVERING_TEST"."SYS_NC00002$">=SYS_EXTRACT_UT
              C(SYSTIMESTAMP(6)) AND "TIMESTAMPTZ_COVERING_TEST"."SYS_NC00002$"<=SYS_E
              XTRACT_UTC(SYSTIMESTAMP(6)+INTERVAL'+00 00:01:00' DAY(2) TO SECOND(0)))

All good now.

When you plan to use TIMESTAMP WITH TIME ZONE as a value from index – you have to convert it back to TIMESTAMP WITH TIME ZONE.

FROM_TZ(SYS_EXTRACT_UTC(indexed_column), 'UTC')

So just keep it in mind. I just had a case where a query like that had to process about 300000 rows, they created a covering index, but it did not help much. 75% of the time was spent on TABLE ACCESS BY ROWID.

Mandatory version information: 19.31