55

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

stic
  • 1,008
  • 3
  • 10
  • 23

5 Answers5

52

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;
Cerbrus
  • 70,800
  • 18
  • 132
  • 147
Jonathan
  • 11,809
  • 5
  • 57
  • 91
  • 1
    Well, the trick is to convert from SYSDATE. So first you will need to somehow establish what SESSIONTIMEZONE is, and use that knowledge in order to get the offset value for SYS_EXTRACT_UTC - won't you? – stic Jul 21 '09 at 09:55
  • Yes, You can check this link from oracle where Datetime and Time Zone Parameters: http://www.oracle.com/technology/obe/obe9ir2/obe-nls/datetime/datetime.htm – Jonathan Jul 21 '09 at 15:01
  • 1
    I have extended the answer due to a subtile bug. – Michael-O Sep 18 '13 at 08:13
47
select sys_extract_utc(systimestamp) from dual;

Won't work on Oracle 8, though.

Juris
  • 1,497
  • 10
  • 7
32

Usually, I work with DATE columns, not the larger but more precise TIMESTAMP used by some answers.

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

CAST(sys_extract_utc(SYSTIMESTAMP) AS DATE)

I often store dates like this, usually with the field name ending in _UTC to make it clear for the developer. This allows me to avoid the complexity of time zones until last-minute conversion by the user's client. Oracle can store time zone detail with some data types, but those types require more table space than DATE, and knowledge of the original time zone is not always required.

Charles Burns
  • 10,310
  • 7
  • 64
  • 81
  • I tried doing comparing 2 dates with this (equals) and was not getting the expected results so I think there is still a time component returned. What did work for me was using the TRUNC statement: Select * From Item Where TRUNC(Create_Date) = ( Select TRUNC(sys_extract_utc(SYSTIMESTAMP)) From DUAL) – DaveK Oct 06 '14 at 17:46
  • 2
    DATE columns do include a time component with accuracy to one second. THey differ from TIMESTAMP columns in that TIMESTAMP stores accuracy greater than one second. Indeed, adding TRUNC to the above is what you want to do if you want to truncate the time component. – Charles Burns Oct 24 '14 at 16:34
8

I'm using:

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

It's working fine for me.

Paul Roub
  • 36,322
  • 27
  • 84
  • 93
Pedro Sobral
  • 81
  • 1
  • 1
5

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

Nathan Strutz
  • 8,095
  • 1
  • 37
  • 48