Oracle SYS_EXTRACT_UTC function

Extract the UTC from a datetime in Oracle

The SYS_EXTRACT_UTC() is used to extract the UTC (Coordinated Universal Time—formerly Greenwich Mean Time) from a datetime value with time zone offset or time zone region name.

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

Uses of Oracle SYS_EXTRACT_UTC() Function

• Extracting UTC from Datetime Values: Convert datetime values with time zone information to UTC.


• Handling Time Zone Variability: Manage and standardize time data regardless of the original time zone.


• Simplifying Time Calculations: Facilitate calculations and comparisons of datetime values by using a common time reference (UTC).


• Supporting Global Applications: Assist applications that operate across multiple time zones by ensuring consistent time data.

Syntax:

SYS_EXTRACT_UTC(datetime_with_timezone)

Parameters:

Pictorial Presentation

Examples: Oracle SYS_EXTRACT_UTC() function

The following example extracts the UTC from a specified datetime:

SQL> SELECT SYS_EXTRACT_UTC(TIMESTAMP '2015-03-18 11:25:00.00 -05:00')
2     FROM DUAL;

Sample Output:

SYS_EXTRACT_UTC(TIMESTAMP'2015-03-1811:25:00.00-05:00')
---------------------------------------------------------------------------
18-MAR-15 04.25.00.000000000 PM

Examples: Oracle SYS_EXTRACT_UTC() function using table

Here is a table: MYTABLE

Here is the code to create a table MYTABLE:

CREATE TABLE  "MYTABLE" 
   (	"ID" NUMBER, 
	"JDATE" TIMESTAMP (6) WITH LOCAL TIME ZONE
   ) ;

Now insert one row into the table. Here is the code:

INSERT INTO MYTABLE VALUES(3, '15-AUG-14');

See the created table:

SELECT * FROM MYTABLE;

ID	JDATE
----------------------------------
3	15-AUG-14 12.00.00.000000 AM

The following example extracts the UTC from a specified column of a table :

SELECT SYS_EXTRACT_UTC(JDATE) AS UTC_DATE FROM MYTABLE;

Sample Output:

          UTC_DATE
-----------------------------
14-AUG-14 06.30.00.000000 PM

Previous: SESSIONTIMEZONE
Next: SYSDATE