I tried the following query on 18c via Oracle’s LiveSQL site and the results don’t correspond to the documented functionality; or, at least, not my reading of the functionality.
select to_utc_timestamp_tz('2018-04-27') t from dual
union all
select to_utc_timestamp_tz('2018-04-27T17:40:25+00:00') from dual
union all
select to_utc_timestamp_tz('2018-04-27T17:40:25Z') from dual
union all
select to_utc_timestamp_tz('20180427T174025Z') from dual
union all
select to_utc_timestamp_tz('2018-04-27T17:40:25+05:00') from dual --- This one is not correct per documented functionality
producing the following results
T 27-APR-18 12.00.00.000000 AM +00:00 27-APR-18 05.40.25.000000 PM +00:00 27-APR-18 05.40.25.000000 PM +00:00 27-APR-18 05.40.25.000000 PM +00:00 27-APR-18 05.40.25.000000 PM +05:00
Note the first four rows all return UTC (+0:00) time zone for the corresponding input value. This is to be expected based not only on the name of the function but also the documentation..
The fifth row though preserves the +5:00 offset. This seems contrary to the documentation:
https://docs.oracle.com/en/database/oracle/oracle-database/18/adjsn/changes.html#GUID-7F43AE55-4176-477E-8D3B-021ED838106D
or
MOS Doc ID 2357879.1
“SQL function to_UTC_timestamp_tz takes as input an ISO 8601 date format string and returns an instance of SQL data type TIMESTAMP WITH TIMEZONE. It normalizes the input to UTC time (Coordinated Universal Time, formerly Greenwich Mean Time).”
By my reading, I would expect any successful transformation to return a timestamp with time zone value in UTC (+0:00) time zone. So is this a misreading on my part, a functionality bug, or a documentation bug? Interestingly, the function is not included in the SQL Reference, only in the JSON Developer’s Guide which isn’t a bug per se, but seems like a documentation oversight.
Oracle support has confirmed this problem and logged it as Bug ID: 27997292.