FB

Oracle SYS_EXTRACT_UTC Function Guide, FAQ, and Examples

In this article, I’ll explain what the SYS_EXTRACT_UTC function does and show you some examples.

Purpose of the Oracle SYS_EXTRACT_UTC Function

The SYS_EXTRACT_UTC function extracts the UTC time from a specified time in a different timezone.

UTC stands for Coordinated Universal Time (yes the words are in a different order to the acronym) and was formerly known as GMT (Greenwich Mean Time).

It’s a great way in Oracle to convert a date to UTC time. Well, specifically you’re converting a datetime with a timezone. There are some examples in this article on how to do this.

Syntax

The syntax of the SYS_EXTRACT_UTC function is:

SYS_EXTRACT_UTC (datetime_with_timezone)

The function returns a TIMESTAMP WITH TIME ZONE data type.

 

Parameters

The parameters of the SYS_EXTRACT_UTC function are:

  • datetime_with_timezone (mandatory): The date value, including timezone, that you wish to find the equivalent UTC time of.

Can You Use SYS_EXTRACT_UTC With SYSDATE?

No, you can’t provide the SYSDATE function as a parameter to the SYS_EXTRACT_UTC function. This is because SYSDATE only has a date and time, and SYS_EXTRACT_UTC needs a timezone component.

However, you can provide the SYSTIMESTAMP function which includes the timezone from the system, including a timezone.

SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP)
FROM dual;

 

Examples of the SYS_EXTRACT_UTC Function

Here are some examples of the SYS_EXTRACT_UTC function. I find that examples are the best way for me to learn about code, even with the explanation above.

If you don’t have any column values to test that have a date and time with a timezone, you can use the TIMESTAMP keyword as shown in these examples.

 

Example 1: Local Timezone

This example finds the UTC time of the current time in your timezone.

SELECT LOCALTIMESTAMP,
SYS_EXTRACT_UTC(LOCALTIMESTAMP) AS utc_time
FROM dual;
LOCALTIMESTAMP UTC_TIME
19-SEP-17 12.17.50.300681 PM 19-SEP-17 07.17.50.300681 PM

This shows the local time and time zone, as well as the equivalent time in UTC time.

 

Example 2: Specific Time

This example shows the UTC time from a specified date and time.

SELECT
SYS_EXTRACT_UTC(TIMESTAMP ‘2017-03-05 14:29:31 -7:00’) AS utc_time
FROM dual;
UTC_TIME
05-MAR-17 09.29.31.000000 PM

This shows the UTC equivalent time of the date that was specified in the function. I’ve used the TIMESTAMP keyword to indicate that the specified value is a timestamp. The timezone is -7 hours, which is specified at the end of the date time value there.

 

Example 3: SYS_EXTRACT_UTC With SYSDATE

Because the input of this function is a datetime with timezone, SYS_EXTRACT_UTC won’t accept SYSDATE as a parameter.

However, you can use the SYSTIMESTAMP function to get the same time, in a timestamp format required for this function.

You can then convert it to a DATE if required.

SELECT SYSTIMESTAMP,
SYS_EXTRACT_UTC(SYSTIMESTAMP) AS timestamp_utc,
CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE) AS date_utc
FROM dual;
SYSTIMESTAMP TIMESTAMP_UTC DATE_UTC
19-SEP-17 12.21.34.771502 PM -07:00 19-SEP-17 07.21.34.771502 PM 19-Sep-17

 

Similar Functions

Some functions which are similar to the SYS_EXTRACT_UTC function are:

  • LOCALTIMESTAMP: Gets the current date and time in your local time zone.
  • SYSDATE: Finds the current date and time in your session’s time zone.
  • FROM_TZ: Converts a TIMESTAMP value and a specified TIME ZONE to a TIMESTAMP WITH TIME ZONE value.

If you want to know more about SQL functions, you can find a full list of Oracle SQL functions here.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.