How to get UTC value for SYSDATE on Oracle

Question

Probably a classic... Would you know a easy trick to retrieve an UTC value of SYSDATE on Oracle (best would be getting something working on the 8th version as well).

For now I've custom function :(

Cheers,

Stefan

Answer 1

If you want a timestamp instead of just a date with sysdate, you can specify a timezone using systimestamp:

select systimestamp at time zone 'UTC' from dual

outputs: 29-AUG-17 06.51.14.781998000 PM UTC

Answer 2

I'm using:

SELECT CAST(SYSTIMESTAMP AT TIME ZONE 'UTC' AS DATE) FROM DUAL;

It's working fine for me.

Answer 3

select sys_extract_utc(systimestamp) from dual;

Won't work on Oracle 8, though.

Answer 4

You can use

SELECT SYS_EXTRACT_UTC(TIMESTAMP '2000-03-28 11:30:00.00 -02:00') FROM DUAL;

You may also need to change your timezone

ALTER SESSION SET TIME_ZONE = 'Europe/Berlin';

Or read it

SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM dual;

Answer 5

Usually, I work with DATE columns, not the larger but more precise TIMESTAMP.

The following will return the current UTC date as just that -- a DATE.

CAST(sys_extract_utc(SYSTIMESTAMP) AS DATE)

Working with UTC dates is great because I don't have to worry about the complexity of time zones. Only the final display to the user needs offsets or other trouble.