Query Oracle DB for timestamps when you know milliseconds
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 :
s开发者_如何学编程elect * 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?
You can use the to_timestamp() function to convert your string into a timestamp value: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions201.htm#sthref2458
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.
Use the java.sql
convenience methods to take that milisecond time and turn it into a Date or Timestamp.
You can use this query:
SELECT * FROM A_TABLE WHERE TIMESTAMP < (SYSDATE - 10/1440)
Where (SYSDATE - 10/1440)
means SYSDATE - 10 Minutes
.
Also see some examples here.
精彩评论