Oracle DATE column – How to convert to UTC

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

I think you might be able to get the results you want with:

select j_title, k_title, l_title, sum(wrk_hr) from
(
  SELECT
   J.J_Title,
   K.K_Title,
   L.L_Title,
   L.Work_ID,
  SUM(CASE WHEN TRUNC(C.CHRG_DT) = TO_DATE('09/27/2013', 'mm/dd/yyyy') THEN C.WRK_HR END) AS WRK_HR
  FROM C, L, K, J
  WHERE J.Project_ID = 'ABC'
  AND K.Project_ID = J.Project_ID
  AND L.Project_ID = K.Project_ID
  AND C.Work_ID = L.Work_ID
  GROUP BY L.Work_ID, L.Project_ID, K.Project_ID, J.J_Title, K.K_Title, L.L_Title
)
group by j_title, k_title, l_title;