How to create an UTC index from a 'DATE' typed column with Oracle? I have a Oracle database with columns of type 'DATE' which store timestamps in local time zone. I need to query this column with timestamps in UTC timezone.

How do I create an index on the column that can be used when queried with UTC timestamps and how to format the query?
To be precise, the date type stores timestamps without any timezone information rather than "in local time zone".


  How do I create an index on the column that can be used when queried with UTC timestamps and how to format the query?


You have three options:


index the date and convert your timestamp to date when querying, eg:

select * from foo where foo_date=cast(systimestamp as date) from dual;

create a functional index on the date converted to a timestamp with local time zone, eg:

create unique index i_foo on foo(cast(ts as timestamp with local time zone));
select * from foo where cast(ts as timestamp with local time zone)=
                        cast(X as timestamp with local time zone);

change the type of your columns of type date to timestamp with time zone or timestamp with local time zone


Of these, it is very likely that the last option is the best one - timestamp with local time zone is the 'correct' type for most data that refers to a fixed point in time. (As opposed to 'noon' which is always 12:00 no matter which time zone you are in.)