delete rows that have a specified timestamp

[Mobo01]

I have a database where I save messages with timestamps. I made the following table using this code:

Code: Select all

CREATE TABLE messages (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    msgid VARCHAR(64) UNIQUE,
    payload TEXT,
    sender VARCHAR(259),
    cur_timestamp TIMESTAMP(3)
);

Everything works flawlessly. However, I am currently attempting to delete some rows with timestamps older than those specified by the user. This is done in the Java component of my system as follows:

Code: Select all

// get current timestamp
Date date = new Date();
// timestamp has to be in the same format as in database
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd h:mm:ss.SSS");
String formattedDate = sdf.format(date);
System.out.println("Current time: " + formattedDate);

// subtract desired time
if (msgOld.equals("hour")) {
  date.setTime(date.getTime() - ONE_HOUR_MILLISCONDS);
  formattedDate = sdf.format(date);
} else if (msgOld.equals("day")) {
  date.setTime(date.getTime() - ONE_DAY_MILLISCONDS);
  formattedDate = sdf.format(date);
}

This also works fine; the constants are as follows: long final ONE HOUR MILLISCONDS = 60 * 60 * 1000;

The only issue is that I'm not sure how to write the query in JDBC. This is what I tried:

Code: Select all

// prepare query
String query;
if (msgOld.equals("all")) {
    query = "TRUNCATE TABLE messages";
} else {
    query = "DELETE FROM messages WHERE cur_timestamp < " + date + ";";
}

However, it indicates that there is an error in my SQL statement (the second one, the one with TRUNCATE works fine). I also tried using formattedDate instead of date in the statement that follows this topic.

What's the problem (possibly something as basic as a discrepancy between java.util.Date and SQL timestamp...)? Thank you for any advice!

[rickyboy]

Start here: http://www.newlisp.org