1

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)

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-2811:30:00.00-08:00')
---------------------------------------------------------------------------
28-MAR-00 07.30.00.000000000 PM
  • 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' – Wernfried Domscheit May 14 '16 at 11:30
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.