Oracle TIMESTAMP query 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 time zones, some UTC, some at other time offsets.

Is there a way I can query the Oracle table so that the results always return as UTC when the corresponding time change was made? Is there anything that can be done by request itself or perhaps change the session? I tried changing the session time zone to Utc, but this only seems to affect the value of CURRENT_TIMESTAMP.

 ALTER SESSION SET TIME_ZONE = 'Utc' 

For example, if the value was saved as:

 21-JAN-10 03.28.38.635000000 PM -05:00 

the request will return as

 21-JAN-10 08.28.38.635000000 PM Utc 

Table definition example

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

Answer 1

You can 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