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)

2 Answers 2

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
1
  • 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' Commented May 14, 2016 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 and acknowledge you have read our privacy policy.

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