A quick way to get UTC time, is with the function: sys_extract_utc. With that, we can quickly get the UTC timestamp. Here is an example to return the UTC time in RFC3399/ISO8601 format:
to_char( sys_extract_utc(systimestamp) , 'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"' )
To return that back into local time, you want to declare a variable with time zone support. So just say the string you have is: 2014-04-17T02:46:16.607Z, you'll want to declare it with a time zone attribute.
l_time timestamp with time zone;
Since Z refers +00:00, you need to set the time zone. If you call to_timezone or to_timezone_tz, without specifying the timezone, it will create the time in the local time zone, so we need to specifically set it with from_tz.
l_time := to_timestamp_tz('2014-04-17T02:46:16.607Z' || '+00:00', 'yyyy-mm-dd"T"hh24:mi.ss.ff3"Z"tzh:tzm');
(Appending the date time string with the time zone info)
l_time := to_timestamp('2014-04-17T02:46:16.607Z', 'yyyy-mm-dd"T"hh24:mi.ss.ff3"Z"'); l_time := from_tz(l_time, '+00:00');
(Setting the time zone after the fact)
Finally, to get the time stamp outputting in your local time zone, you can do:
l_time := l_time at local;
(Converting it to the time zone of the database)
Or, specifically set what time zone to convert it to:
l_time := l_time at time zone '+10:00';
l_time := l_time at time zone sessiontimezone;
l_time := l_time at time zone 'Australia/Sydney';
Where the time zone string can be a valid time zone name as from the v$timezone_names view; or the GMT offset, as per the example above.
If all you want to do is get it in the local time zone of the database, you can declare the time stamp with local time zone, and this will automatically output the time in the databases local time zone.
declare l_timestamp timestamp with local time zone; begin l_timestamp := to_timestamp('2014-04-17T02:46:16.607Z', 'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"'); l_timestamp := from_tz(l_timestamp, '+00:00'); dbms_output.put_line(l_timestamp ); end;
This seems like as good a time as any to spruik the fact that Application Express comes with support for local time stamps since version 4. You can read more about that here: http://joelkallman.blogspot.com.au/2010/09/automatic-time-zone-support-in.html