sql - Oracle UTC Time -

sql - Oracle UTC Time -

trying read oracle timestamp timezone dbase in utc-5(ny) utc.

oracle driving me crazy:

select from_tz(max(timestampwithtimezone),'utc'), sys_extract_utc(max(timestampwithtimezone)), sys_extract_utc(systimestamp), systimestamp @ time zone 'utc' table

okay,

sys_extract_utc(systimestamp) gives me: 2013-02-20 14:59:04, right.

systimestamp @ time zone 'utc' gives me: 2013-02-20 15:59:04 own local berlin - whatever

now want have timestampwithtimezone ( timestamp(6) ) utc

sys_extract_utc(max(timestampwithtimezone)) 2013-02-20 08:55:01

from_tz(max(timestampwithtimezone),'utc') 2013-02-20 10:55:01

srly. oracle. want utc.

which 1 right one? or there improve way? in advance.

the functions different:

sys_extract_utc converts timestamp timezone timestamp (with inferred absent timezone=utc). from_tz converts timestamp timestamp timezone

these functions when applied single value in general homecoming different result:

sql> select sys_extract_utc(localtimestamp) ext, 2 from_tz(localtimestamp, 'utc') from_tz 3 dual; ext from_tz --------------------- ------------------------ 2013/02/20 15:34:24 2013/02/20 16:34:24 utc

in first case timestamp implicitly given timezone of server , transformed equivalent timestamp @ utc timezone. note in general should remain away implicit conversions.

in sec case there no computation between timezones: from_tz function adds geographical location point in time variable.

by way there missing in example: can't apply from_tz function on variable of type timestamp timezone (tested on 9ir2 , 11ir2):

sql> select from_tz(systimestamp, 'utc') dual; select from_tz(systimestamp, 'utc') dual ora-00932: inconsistent datatypes: expected timestamp got timestamp time zone

edit next comment:

in case assuming column of time timestamp, , knowing refer ny timezone, utilize at time zone look convert utc:

sql> select localtimestamp, 2 from_tz(localtimestamp, 'america/new_york') @ time zone 'utc' utc 3 dual; localtimestamp utc --------------------- ------------------------ 2013/02/20 17:09:09 2013/02/20 22:09:09 utc

sql oracle oracle11g timezone