Andrew Fraser DBA

Convert java dates to oracle sql dates (esp. odm_publicobject createdate)

Java dates are recorded in milliseconds after 01-Jan-1970 00:00:00 GMT. To convert these to oracle dates, use sql like:

SELECT TO_DATE('01-JAN-1970 00:00:00','DD-MON-YYYY HH24:MI:SS') + createdate/(1000*60*60*24) createdate ,
       creator
  FROM odm_publicobject
 WHERE TO_DATE('01-JAN-1970 00:00:00','DD-MON-YYYY HH24:MI:SS') + createdate/(1000*60*60*24) > sysdate - 30
 ORDER BY 1 , 2 ;

Note that the above does not correct for any differences between local time and GMT. I didn’t need that level of accuracy, but if you did, you could use the TIMESTAMP datatype.