Inconsistent java calendar results
I have a cron job to clear data older than a certain month (user-supplied). The cron task calls a method purgeData()
that clears data from the postgres table. I am manipulating java Calendar
from the current date (through GregorianCalendar.getInstance
) to determine the end date at which to delete data before.
My problem is that calendar manipulation and / or converting a newly managed calendar to a string for use in postgres is not randomly done, setting the target date to the current date, which deletes everything up to the current date, not data older than 1 (or # months for data storage).
Here's my simple date format:
public static final SimpleDateFormat dateFormatter = new SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss.SSS");
Here's my method:
public String purgeData() throws ParseException {
Connection con = null;
String sqlString = "";
PreparedStatement pst = null;
String returnString = "";
Calendar startDate = GregorianCalendar.getInstance();
returnString += "# Months to keep data: " + getNumMonthsKeepData();
startDate.add(Calendar.MONTH, getNumMonthsKeepData() * -1);
String targetDate = dateFormatter.format(startDate.getTime());
Calendar today = GregorianCalendar.getInstance();
returnString +=" Target date (string): " + targetDate + " start date (Calendar): " + startDate.toString() + ", Start month: " + startDate.get(Calendar.MONTH) + ", Current month: " + today.get(Calendar.MONTH);
if (startDate.get(Calendar.MONTH)!= today.get(Calendar.MONTH)) {
String tableName = getPreviousMonthlyTable();
try {
con = getDBConnection();
try {
// Delete old data
sqlString = "DELETE FROM \"" + tableName
+ "\" WHERE datetime < '" + targetDate + "'";
pst = con.prepareStatement(sqlString);
int rowsDeleted = pst.executeUpdate();
returnString += "SUCCESS: Purged data prior to " + targetDate
+ " # rows deleted: " + rowsDeleted
+ "( # rows deleted last purge: "
+ numRowsDeletedPreviously + " )\n";
} catch (SQLException ex) {
returnString += "FAILED to execute: " + sqlString;
}
try {
if (pst != null) {
pst.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
return null;
}
} catch (SQLException ex) {
returnString += "Delete from table fail: " + ex.getMessage();
}
} else {
returnString += "FAIL: Fail to delete data prior to: " + targetDate + ". Start month: " + startDate.get(Calendar.MONTH)
+ " equals current month: " + today.get(Calendar.MONTH);
}
return returnString;
}
The date error appears to be random since when it succeeds in one deployment, it fails in another.
Failure output:
20150421-00: 33: 11.006 Postgres Notification - Cleanup: # month to store data: 1 Target date (string): 2015-04-21 00: 00: 00.001 , Start month: 2, Current month: 3 SUCCESS: cleaned data before 2015-04-21 00: 00: 00.001 # lines deleted: 7575704 (# lines deleted last cleanup: 26608)
NOTE: Target date should be 2015-03-21 00: 00: 30.000 (note that this is also 30 minutes since the cron job runs every 4 hours starting at 00:30)
Old crash results (before adding more logs): 20150414-20: 37: 53.347 Postgres Notification - Cleanup: SUCCESS: Missing data before 2015-04-14 19: 00: 00.004 # lines deleted: 12195291 (# lines deleted last cleanup: 128570)
NOTE: The cleanup data is up to 2015-03-14 20: 30: 00.000 (note that this is also 1 hour and 30 minutes since the cron job runs every 4 hours starting at 00:30)
Success exit: 20150421-00: 30: 02.559 Postgres Notification - Cleanup: # Months to store data: 1 Target date (string): 2015-03-21 00: 30: 00.003, Start month: 2, Current month: 3 SUCCESS: cleared data before 2015-03-21 00: 30: 00.003 # rows deleted: 139757 (# of rows deleted last cleared: 33344)
It looks like the date manipulation actually works as shown in the output of the start month and current month. In both cases with failures, the integer values are different. However, converting the string to SimpleDateFormat seems to be wrong.
I read the javadocs that when setting up fields in the Calendar one has to call get () for the time to be recalculated. However, add () should force redistribution.
source to share
Your date format is not thread safe, storing it in a class variable and letting multiple threads hammer on it at the same time will produce invalid results.
This is described in the API documentation for SimpleDateFormat under the heading Synchronization:
Date formats are not syncing. It is recommended that you create separate instances of the format for each stream. If multiple threads access the format at the same time, it must be synchronized externally.
One fix is for your method to create its own SimpleDateFormatter instance. There are more options listed in the next question here: Creating a DateFormat Threadsafe. What to use, sync or localize thread .
However, date formatting is not required, you can instead pass the date to the PreparedStatement as an argument:
sqlString = "DELETE FROM \"" + tableName + "\" WHERE datetime < ?";
pst = con.prepareStatement(sqlString);
pst.setTimestamp(1, new Timestamp(startDate.getTime()));
source to share