Query Oracle DB for timestamps when you know milliseconds

Question

Hi,

I'm currently getting the error: "java.sql.SQLException: ORA-01843: not a valid month" which I assume is to do with the way I am setting a timestamp...

So I have a query like this :

select * from A_TABLE where A_TIMESTAMP_COL < '1252944840000'

But it doesn't work...and I don't want to have to convert it to a date ideally. Is there some special syntax to tell Oracle that this is a timestamp?

Thanks in advance.

Answer 1

Use the java.sql convenience methods to take that milisecond time and turn it into a Date or Timestamp.

Answer 2

I think you can cast the date column to a char and do something like:

select * from A_TABLE where to_char(A_TIMESTAMP_COL) < '1252944840000'

This should allow you to compare strings, not dates.

Answer 3

You can use the to_timestamp() function to convert your string into a timestamp value: http://download.oracle.com/docs/cd/B28359%5F01/server.111/b28286/functions201.htm#sthref2458

Answer 4

You can use this query: SELECT * FROM A_TABLE WHERE TIMESTAMP < (SYSDATE - 10/1440)

(SYSDATE - 10/1440) means SYSDATE - 10 Minutes. See also some examples here: http://www.orafaq.com/faq/how%5Fdoes%5Fone%5Fadd%5Fa%5Fday%5Fhour%5Fminute%5Fsecond%5Fto%5Fa%5Fdate%5Fvalue