Oracle TO_UTC_TIMESTAMP_TZ Function

JSON documents and REST services exchange dates and times as ISO 8601 strings, such as 2026-03-15T08:30:00+04:00 or 2026-03-15T08:30:00Z. Converting them with TO_TIMESTAMP_TZ needs a format model that matches the T, the fraction, and the offset exactly. TO_UTC_TIMESTAMP_TZ reads ISO 8601 strings directly, with no format model, and returns a TIMESTAMP WITH TIME ZONE.

Code for This Guide

The main example is in the examples/conversion-functions folder of the Oracle Database 26ai code repository on GitHub, with its output. The repository also has the NIMBUS sample schema in the setup/nimbus folder.

It comes from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

to_utc_timestamp_tz(varchar2)

The argument is an ISO 8601 date or timestamp: a date alone (YYYY-MM-DD), or a date and time joined by T, optionally with fractional seconds and with an offset such as +04:00 or the letter Z for UTC.

Convert ISO 8601 Strings

Example:

select to_utc_timestamp_tz('2026-03-15T08:30:00+04:00') as from_offset,
       to_utc_timestamp_tz('2026-03-15T08:30:00Z')      as from_utc,
       to_utc_timestamp_tz('2026-03-15')                as date_only
from   dual;

Output:

FROM_OFFSET                    FROM_UTC                    DATE_ONLY
______________________________ ___________________________ ___________________________
15-MAR-2026 08:30:00 +04:00    15-MAR-2026 08:30:00 GMT    15-MAR-2026 00:00:00 GMT

A string with an offset keeps that offset. Z means UTC, shown as GMT. A date alone becomes midnight UTC.

Get the UTC Time

Despite its name, the function does not move the value to UTC: 08:30 +04:00 stays 08:30 +04:00, which is the same moment as 04:30 UTC. Apply SYS_EXTRACT_UTC when you need the UTC clock time, for example to store it in a TIMESTAMP column without a time zone.

Example:

-- the result keeps the offset of the string; SYS_EXTRACT_UTC gives the UTC time
select to_utc_timestamp_tz('2026-03-15T08:30:00+04:00')                   as with_offset,
       sys_extract_utc(to_utc_timestamp_tz('2026-03-15T08:30:00+04:00'))  as in_utc,
       to_utc_timestamp_tz('2026-03-15T08:30:00.250-05:00')               as with_fraction
from   dual;

Output:

WITH_OFFSET                    IN_UTC                  WITH_FRACTION
______________________________ _______________________ ______________________________
15-MAR-2026 08:30:00 +04:00    15-MAR-2026 04:30:00    15-MAR-2026 08:30:00 -05:00

Fractional seconds are accepted; the display shows them only if the session's timestamp format includes them.

Non-ISO Strings Fail

A string in another layout raises an error, so use TO_TIMESTAMP_TZ with a format model for those.

Example:

select to_utc_timestamp_tz('15/03/2026 08:30') as not_iso from dual;

Output:

Error starting at line : 1
In command -
select to_utc_timestamp_tz('15/03/2026 08:30') as not_iso from dual
Error at Command Line : 1 Column : 64
Error report -
SQL Error: ORA-01830: Date format picture ends before converting entire input string.

Related Guides

• Oracle TO_TIMESTAMP_TZ Function: A Simple Guide
• Oracle SYS_EXTRACT_UTC Function: A Simple Guide
• Oracle Datetime Format Models Explained

Conclusion

TO_UTC_TIMESTAMP_TZ converts ISO 8601 date and timestamp strings into TIMESTAMP WITH TIME ZONE without a format model, reading offsets and Z for UTC, and treating a date alone as midnight UTC. The result keeps the string's offset; wrap it in SYS_EXTRACT_UTC to get the UTC time.