Oracle DATE column - How to convert to UTC

Question

I have an oracle 10g table with a DATE column type. I need to compare it in a where clause to a value generated by a script that looks like this: TIMESTAMP '2016-05-12 08:00:00.258000 +00:00'. I cannot control the generated TIMESTAMP part, and when I do:

RECORDEDTIME >= TIMESTAMP '2016-05-12 08:00:00.258000 +00:00' it uses the localtime of the RECORDEDTIME which means I am 4 hours off. What function do I call on RECORDEDTIME to get it in UTC matching the other value in the WHERE clause (i.e. it needs to subtract 4 hours as I'm in EST)

Answer 1

SYS_EXTRACT_UTC

SQL> SELECT SYS_EXTRACT_UTC(TIMESTAMP '2000-03-28 11:30:00.00 -08:00') FROM DUAL;
SYS_EXTRACT_UTC(TIMESTAMP'2000-03-.&checktime(2811,30,00,':').00-08:00')
---------------------------------------------------------------------------
28-MAR-00 07.30.00.000000000 PM

• Wernfried Domscheit: I assume it would be rather WHERE SYS_EXTRACT_UTC(RECORDEDTIME) = TIMESTAMP '2016年05月12日 08:00:00.258000 +00:00', resp. WHERE SYS_EXTRACT_UTC(CAST(RECORDEDTIME) AS TIMESTAMP) AT '+04:00')= TIMESTAMP '2016年05月12日 08:00:00.258000 +00:00'

Answer 2

I would suggest to use where recordtime >= TIMESTAMP '2016-05-12 08:00:00.258000 +00:00' AT LOCAL