How to convert sysdate to UTC time in oracle

Question

I am having a query to subtract a date from the sysdate. However the date object I have is in UTC but sysdate doesn't give me UTC time. How to I convert sysdate to give me date in UTC.

I have already tried using sys_extract_utc

select sys_extract_utc(systimestamp) from dual;

This works fine and gives me the correct UTC time stamp. But the object returned by it is not the same as sysdate so when I subtract date from this object I get an INTERVAL DAY to SECOND but it should be a number.

Answer 1

Using CAST on the result of sys_extract_utc(systimestamp) is the solution to this since sys_extract_utc returns timestamp.

select cast(sys_extract_utc(systimestamp) as DATE) from dual;

Answer 2

The sys_extract_utc returns timestamp, and you are looking for date, so you just need a cast: select CAST(sys_extract_utc(systimestamp) as DATE) from dual;

Answer 3

In case you do not want the "time" portion:

SELECT TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'YYYY-MM-DD') FROM dual;