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,
);
In Oracle, date format and timezones feature multiple data types, including DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE.
Oracle does note store the data in specified format. You can use SYS_EXTRACT_UTC to get TIMESTAMP in UTC Timezone.
TIMESTAMP WITH TIME ZONE has the same precision as TIMESTAMP but also has time zone support, as the name suggests; TIMESTAMP WITH LOCAL TIME ZONE adjusts the stored value to and from the creating/querying session's local time zone.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With