5

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,
);
Stealth Rabbi
  • 10,156
  • 22
  • 100
  • 176

1 Answers1

13

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
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • I'm guessing there's no way I can alter the session so the logic of "AT TIME ZONE 'UTC'" is automatic? – Stealth Rabbi Feb 10 '12 at 15:54
  • 2
    @StealthRabbi - If the column was `TIMESTAMP WITH LOCAL TIME ZONE`, the value would be implicitly converted to the session's time zone which could be set to UTC. A `TIMESTAMP WITH TIME ZONE` is displayed in whatever time zone it was inserted with unless you convert it with something like the `AT TIME ZONE` syntax. – Justin Cave Feb 10 '12 at 16:02