How to create an UTC index from a ‘DATE’ typed column with Oracle

Since the data is stored in a TIMESTAMP column, you cannot automatically convert it. You'll have to write some code that tells Oracle what time zone to treat the data as being stored in the GMT time zone and tell it that you want it converted to the session's time zone. You would get the automatic behavior you are looking for if you used the TIMESTAMP WITH LOCAL TIME ZONE data type instead. If you used the TIMESTAMP WITH TIME ZONE data type, you would avoid having to tell the database what time zone the timestamp comes from when you write the conversion.

Assuming that your session sets its time zone properly

ALTER SESSION SET time_zone = '-8:00'

for example, would tell Oracle that the current session is 8 hours before GMT (currently the Pacific time zone), if the data is stored in a TIMESTAMP column, you'd need something like

FROM_TZ( ts, 'GMT' ) AT TIME ZONE sessiontimezone

If the data is stored in a TIMESTAMP WITH TIME ZONE column, you'd just need

ts_tz AT TIME ZONE sessiontimezone

If the data is stored in a TIMESTAMP WITH LOCAL TIME ZONE column, you would just select that column.

An example

SQL> ed
Wrote file afiedt.buf

  1  create table foo(
  2    ts timestamp,
  3    ts_tz timestamp with time zone,
  4*   ts_local_tz timestamp with local time zone )
SQL> /

Table created.

SQL> ed
Wrote file afiedt.buf

  1  insert into foo
  2    values( sys_extract_utc( systimestamp ),
  3            systimestamp,
  4*           systimestamp )
SQL> /

1 row created.

SQL> select from_tz( ts, 'GMT' ) at time zone sessiontimezone ,
  2         ts_tz at time zone sessiontimezone,
  3         ts_local_tz
  4    from foo;

FROM_TZ(TS,'GMT')ATTIMEZONESESSIONTIMEZONE
---------------------------------------------------------------------------
TS_TZATTIMEZONESESSIONTIMEZONE
---------------------------------------------------------------------------
TS_LOCAL_TZ
---------------------------------------------------------------------------
02-MAY-13 01.59.03.171000 PM -08:00
02-MAY-13 01.59.03.171000 PM -08:00
02-MAY-13 01.59.03.171000 PM

In response to the comment below from DylanKlomparens -- you cannot just apply the AT TIME ZONE to a TIMESTAMP and expect to get the proper result so I'm not sure that I understand what you are seeing. If you just to an AT TIME ZONE on a plain TIMESTAMP, the timestamp is simply treated as having the time zone that you specified. It doesn't change the time. It also doesn't depend on the session time zone. In all these cases, if ts represents a UTC time, the PST version represents the wrong time-- there should be an 8 hour difference between the starting and ending values.

SQL> select ts from foo;

TS
---------------------------------------------------------------------------
02-MAY-13 09.59.03.171000 PM

SQL> select ts at time zone 'PST' from foo;

TSATTIMEZONE'PST'
---------------------------------------------------------------------------
02-MAY-13 09.59.03.171000 PM PST

SQL> alter session set time_zone = '-0:00';

Session altered.

SQL> select ts at time zone 'PST' from foo;

TSATTIMEZONE'PST'
---------------------------------------------------------------------------
02-MAY-13 09.59.03.171000 PM PST

SQL> alter session set time_zone = '-4:00';

Session altered.

SQL> select ts at time zone 'PST' from foo;

TSATTIMEZONE'PST'
---------------------------------------------------------------------------
02-MAY-13 09.59.03.171000 PM PST

In response to Vincent's comment-- A TIMESTAMP WITH LOCAL TIME ZONE does adjust automatically to the session's time zone regardless of the server's time zone. As I adjust my session's time zone, the results change as well.

SQL> select ts_local_tz from foo;

TS_LOCAL_TZ
---------------------------------------------------------------------------
02-MAY-13 05.59.03.171000 PM

Elapsed: 00:00:00.00
SQL> alter session set time_zone = '-0:00';

Session altered.

Elapsed: 00:00:00.00
SQL> select ts_local_tz from foo;

TS_LOCAL_TZ
---------------------------------------------------------------------------
02-MAY-13 09.59.03.171000 PM

Elapsed: 00:00:00.00
SQL> alter session set time_zone = '+4:00';

Session altered.

Elapsed: 00:00:00.00
SQL> select ts_local_tz from foo;

TS_LOCAL_TZ
---------------------------------------------------------------------------
03-MAY-13 01.59.03.171000 AM

Elapsed: 00:00:00.00

Oracle's DATE datatype behaves as both an ANSI DATE and TIME datatype. This was a design decision that only Oracle knows the reasoning for.

I'll add that Oracle isn't the only RDBMS that doesn't follow ANSI SQL standards in this way.

Obviously, you can remove the time portion with TO_CHAR(departure_date,'DD-MM-YYYY').