delete rows that have a specified timestamp

Machine-specific discussion
Unix, Linux, OS X, OS/2, Windows, ..?
Locked
Mobo01
Posts: 8
Joined: Thu Jun 23, 2022 8:57 am

delete rows that have a specified timestamp

Post by 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
Posts: 607
Joined: Fri Apr 08, 2005 7:13 pm
Location: Front Royal, Virginia

Re: delete rows that have a specified timestamp

Post by rickyboy »

(λx. x x) (λx. x x)

Locked