delete rows that have a specified timestamp
Posted: Sun Apr 30, 2023 7:20 pm
I have a database where I save messages with timestamps. I made the following table using this code:
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:
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:
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!
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)
);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);
}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 + ";";
}What's the problem (possibly something as basic as a discrepancy between java.util.Date and SQL timestamp...)? Thank you for any advice!