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.