Converting a DATE to DATE in UTC time : sys_extract_utc behaving differently for sysdate and systimestamp

user_2235839 0
Viewed 1437
select sysdate from dual;    
15-NOV-2018 23:09:31

select cast(sysdate as TIMESTAMP) from dual;
15-NOV-18 11.07.47.000000000 PM

select sys_extract_utc(cast(sysdate as TIMESTAMP)) from dual;
15-NOV-18 05.40.35.000000000 PM

But,

select systimestamp from dual;
15-NOV-18 11.11.16.345959000 PM -08:00

select sys_extract_utc(systimestamp) from dual;
16-NOV-18 07.08.49.772214000 AM

Further,

select cast(sysdate as TIMESTAMP) at time zone 'UTC' from dual;
15-NOV-18 05.48.11.000000000 PM UTC

Why is sys_extract_utc(cast(sysdate as TIMESTAMP)) different than sys_extract_utc(systimestamp) in my db?
The time given by sys_extract_utc(systimestamp) is the correct UTC time, btw.

My core requirement is converting data from a table with a DATE column to "DATE in UTC time zone" in a select query. It is of course not working (with either sys_extract_utc or with at time zone 'UTC'), which is probably because of the same reason the above conversion with sysdate is not working.

In case it helps, dbtimezone is:

SELECT DBTIMEZONE FROM DUAL;
+00:00
oracle sql
1 Answers

Function SYS_EXTRACT_UTC works only with TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE values. DATE and TIMESTAMP do not contain any time zone information, so SYS_EXTRACT_UTC is basically useless on DATE or TIMESTAMP values.

When you run 

SYS_EXTRACT_UTC(CAST(SYSDATE AS TIMESTAMP))

then Oracle performs an implicit cast and actually runs

SYS_EXTRACT_UTC(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), SESSIONTIMEZONE))

SYSDATE and SYSTIMESTAMP returns time in time zone of database server's operating system (NOT DBTIMEZONE) whereas your command uses the current session SESSIONTIMEZONE

If you insist to use a DATE value then you could use this one:

SYS_EXTRACT_UTC(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), TO_CHAR(SYSTIMESTAMP, 'tzr')))

TO_CHAR(SYSTIMESTAMP, 'tzr') provides you the time zone of database server's operating system.

Just a note, very often the time zone of database server's operating system is not a region like Europe/Zurich but altering twice a year between +01:00 and +02:00 due to daylight saving times. For SYSDATE and SYSTIMESTAMP this does not matter because the current UTC offset is always correct. But for example, if my table contains a DATE value 2018-08-01 12:00:00 then you will get wrong result if you run query from above today in November.

If your database server utilize daylight saving times then better use a query like

SYS_EXTRACT_UTC(FROM_TZ(CAST({arbitrary DATE value} AS TIMESTAMP), 'Europe/Zurich'))
user_30272660
Related