How to create an UTC index from a 'DATE' typed column with Oracle?
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.)