Querying Oracle TIMESTAMP WITH TIMEZONE
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
Stealth Rabbi
Tea. Earl Gray. Hot. Welcome to Earth. I dabble in .NET, Java, Android, SQL Server, Oracle, Google Earth, and some other things.
Updated on February 10, 2020Comments
-
Stealth Rabbi over 4 years
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, );