File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes how do I get the last day rows in mysql? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "how do I get the last day rows in mysql?" Watch "how do I get the last day rows in mysql?" New topic

how do I get the last day rows in mysql?

david dabush

Joined: Sep 16, 2012
Posts: 27
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+"' ");

Martin Vajsar

Joined: Aug 22, 2010
Posts: 3733

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.)
    I agree. Here's the link:
    subject: how do I get the last day rows in mysql?
    It's not a secret anymore!