SYS_EXTRACT_UTC

Syntax

SYS_EXTRACT_UTC
(
  time  IN timestamp with time zone
)
RETURNS timestamp without time zone;

SYS_EXTRACT_UTC
(
  time  IN timestamp without time zone
)
RETURNS timestamp without time zone;

Overview

The SYS_EXTRACT_UTC function converts the given timestamp (with/without time zone) to a timestamp in UTC (without timezone) and returns it.

For TIMESTAMP (without time zone), the value is interpreted based on the time zone of the session, then converted to a timestamp in UTC and returned.

In other words, theTIMESTAMP without time zone type is first converted to a TIMESTAMP WITH TIME ZONE, then shifted to the "utc" time zone and returned like a DATE in Oracle.

Parameters

Parameter	Description
time	timestamp, timestamptz type: the target timestamp value to convert to UTC. For timestamp, it is internally converted to timestamptz based on the timezone of the session and then converted to UTC.

Example

# test 1
SELECT oracle.SYS_EXTRACT_UTC('2023-06-01 12:34:56+07'::timestamptz);

   sys_extract_utc   
---------------------
 2023-06-01 05:34:56
(1 row)

# test 2
-- Convert to timestamptz based on the session time zone '+09:00', then shift to UTC. 
SELECT oracle.SYS_EXTRACT_UTC('2023-06-01 12:34:56'::timestamp);

   sys_extract_utc   
---------------------
 2023-06-01 03:34:56
(1 row)