w3resource

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.

Syntax:

SYS_EXTRACT_UTC(datetime_with_timezone)

Parameters:

Name Description
datetime_with_timezone Specified date time with timezone

Pictorial Presentation

Pictorial Presentation of Oracle SYS_EXTRACT_UTC function

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



Share this Tutorial / Exercise on : Facebook and Twitter