Querying Oracle TIMESTAMP WITH TIMEZONE

Question

I have a column in an Oracle DB table that is of type TIMESTAMP(6) WITH TIME ZONE. There are data rows with data from different timezones, some UTC, some in other timezone offsets.

Is there a way I can query the Oracle table so that the results always come back as UTC, with the appropriate time shifting being done? Is there something that can be done on the query itself, or perhaps altering the session somehow? I've tried altering the session timezone to Utc, but this seems to only impact the CURRENT_TIMESTAMP value.

ALTER SESSION SET TIME_ZONE = 'Utc'

For example, if a value was stored as:

21-JAN-10 03.28.38.635000000 PM -05:00

the query would come back as

21-JAN-10 08.28.38.635000000 PM Utc

Example table definition

CREATE TABLE "MyDb"."Books"
  (
    "GUID" RAW(32) DEFAULT SYS_GUID(),
     "DATE_CREATED" TIMESTAMP (6) WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
);

Justin Cave Justin Cave · Accepted Answer · 2012-02-09T18:50:48

You should be able to use the AT TIME ZONE syntax

SELECT column_name at time zone 'UTC'
  FROM your_table

i.e.

SQL> select * from foo;

COL1
---------------------------------------------------------------------------
09-FEB-12 01.48.40.072000 PM -05:00
09-FEB-12 10.49.26.613000 AM US/PACIFIC

SQL> select col1 at time zone 'UTC'
  2    from foo;

COL1ATTIMEZONE'UTC'
---------------------------------------------------------------------------
09-FEB-12 06.48.40.072000 PM UTC
09-FEB-12 06.49.26.613000 PM UTC

Querying Oracle TIMESTAMP WITH TIMEZONE

1 Answers