In Oracle Database, the SYS_EXTRACT_UTC() function extracts the UTC (Coordinated Universal Time) from a datetime value with time zone offset or time zone region name.

Syntax

The syntax goes like this:

SYS_EXTRACT_UTC(datetime_with_timezone)

Example

Here’s an example to demonstrate:

SELECT 
SYS_EXTRACT_UTC(TIMESTAMP '2035-08-25 10:30:45.123456789 +04:00')
FROM DUAL;

Result:

25/AUG/35 06:30:45.123456789 AM

The resulting format depends on the value of your NLS_TIMESTAMP_FORMAT parameter. This parameter’s default value is derived from the NLS_TERRITORY parameter. Changing any of these can change the format of the SYS_EXTRACT_UTC() function for your session.

Example:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-RRRR HH24:MI:SSXFF';
SELECT 
SYS_EXTRACT_UTC(TIMESTAMP '2035-08-25 10:30:45.123456789 +04:00')
FROM DUAL;

Result:

25-AUG-2035 06:30:45.123456789

And here’s what it looks like when I set the NLS_TERRITORY parameter to AMERICA:

ALTER SESSION SET NLS_TERRITORY = 'AMERICA';
SELECT 
SYS_EXTRACT_UTC(TIMESTAMP '2035-08-25 10:30:45.123456789 +04:00')
FROM DUAL;

Result:

25-AUG-35 06.30.45.123456789 AM

Changing this parameter implicitly set the NLS_TIMESTAMP_FORMAT to the format for AMERICA.

See How to Change your Session’s Date Format for more information and examples.

Time Zone Region Name

You can alternatively use the time zone region name instead of the time zone offset.

Example:

SELECT 
SYS_EXTRACT_UTC(TIMESTAMP '2035-08-25 10:30:45.123456789 Asia/Bangkok')
FROM DUAL;

Result:

25-AUG-35 03.30.45.123456789 AM

Default Time Zone

If a time zone is not specified, then the datetime is associated with the session time zone.

Example:

SELECT SYS_EXTRACT_UTC(TIMESTAMP '2035-08-25 10:30:45.123456789')
FROM DUAL;

Result:

25-AUG-35 12.30.45.123456789 AM

You can check the current session’s time zone with the SESSIONTIMEZONE function:

SELECT SESSIONTIMEZONE
FROM DUAL;

Result:

Australia/Brisbane

See 4 Ways to Change the Time Zone in Oracle for examples of changing this.

Null Arguments

Passing null results in an error:

SELECT SYS_EXTRACT_UTC(null)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT SYS_EXTRACT_UTC(null)
FROM DUAL
Error at Command Line : 1 Column : 24
Error report -
SQL Error: ORA-30175: invalid type given for an argument
30175. 00000 -  "invalid type given for an argument"
*Cause:    There is an argument with an invalid type in the argument list.
*Action:   Use the correct type wrapper for the argument.

Missing Argument

Calling the function with the wrong number of arguments, or without passing any arguments, results in an error:

SELECT SYS_EXTRACT_UTC()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT SYS_EXTRACT_UTC()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action: