Oracle UTC Time
The functions are different:
SYS_EXTRACT_UTC
converts aTIMESTAMP WITH TIMEZONE
to aTIMESTAMP
(with inferred but absent timezone=UTC).FROM_TZ
converts aTIMESTAMP
to aTIMESTAMP WITH TIMEZONE
These functions when applied to a single value will in general return a different result:
SQL> SELECT sys_extract_utc(localtimestamp) ext, 2 from_tz(localtimestamp, 'UTC') from_tz 3 FROM dual;EXT FROM_TZ--------------------- ------------------------2013/02/20 15:34:24 2013/02/20 16:34:24 UTC
In the first case the TIMESTAMP
is implicitly given the timezone of the server and then transformed into the equivalent timestamp at the UTC timezone. Note that in general you should stay away from implicit conversions.
In the second case there is no computation between timezones: the FROM_TZ
function adds the geographical location to a point in time variable.
By the way there is something missing in your example: you can't apply the FROM_TZ
function on a variable of type TIMESTAMP WITH TIMEZONE
(tested on 9ir2 and 11ir2):
SQL> select from_tz(systimestamp, 'UTC') from dual;select from_tz(systimestamp, 'UTC') from dualORA-00932: inconsistent datatypes: expected TIMESTAMP got TIMESTAMP WITH TIME ZONE
Edit following comment:
In your case assuming that your column are of time TIMESTAMP
, and knowing that they refer to the NY timezone, you could use the AT TIME ZONE
expression to convert to UTC
:
SQL> SELECT localtimestamp, 2 from_tz(localtimestamp, 'America/New_York') AT TIME ZONE 'UTC' utc 3 FROM dual;LOCALTIMESTAMP UTC--------------------- ------------------------2013/02/20 17:09:09 2013/02/20 22:09:09 UTC
From Oracle 18c you could use TO_UTC_TIMESTAMP_TZ function:
The TO_UTC_TIMESTAMP_TZ function converts any valid ISO 8601 date represented as a string into a TIMESTAMP WITH TIMEZONE, which can optionally be used as input to the SYS_EXTRACT_UTC function.
SELECT TO_UTC_TIMESTAMP_TZ(col_name)FROM tab_name;