• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how do I get the last day rows in mysql?

 
david dabush
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hey,
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.

in Oracle:
DateFormat dateFormat = new SimpleDateFormat("dd-MMM-YYYY");
Date date = new Date();
String currentDate=dateFormat.format(date);
ResultSet rsOracle=oracleStmt.executeQuery("select * from table where " +
" TRUNC(SCN_TO_TIMESTAMP(ORA_ROWSCN)) = '"+currentDate+"' ");

thanks
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

    (By the way, you really should use PreparedStatements.)
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic