i have a table in mysql DB and i want to know how can i get the rows in this table that i inserted in some specific date.
i know how how to do in Oracle but here it is something diffrent i guess. and NO I dont have column with a date attribute.
DateFormat dateFormat = new SimpleDateFormat("dd-MMM-YYYY");
Date date = new Date();
ResultSet rsOracle=oracleStmt.executeQuery("select * from table where " +
" TRUNC(SCN_TO_TIMESTAMP(ORA_ROWSCN)) = '"+currentDate+"' ");
ORA_ROWSCN is Oracle specific. I'm not sure MySQL has anything similar at all.
Even in Oracle, ORA_ROWSCN can behave a bit weird sometimes:
It cannot be used to implement optimistic locking.
SCNs that are farther in the past cannot even be translated to time; Oracle doesn't keep the scn to timestamp mapping forever.
You need to create the table with ROWDEPENDENCIES, otherwise the SCN is kept at the block level, instead of row level.
And if you update a row, it's ROWSCN changes, so it is not an insert date, but a last modification date.
I would use a separate column for this even in Oracle. You can declare it default sysdate, so that your application won't even need to change the way it inserts rows. MySQL probably provides similar construct.