I am writing a method to determine if a mysql table has been modified since I last looked at it, to keep from having to load it again unless necessary. I have been able to get the last modified time and compare it to my current time, but unfortunately the sql server is located 3 hours ahead of me, and it causes issues. Please see the code below. Thank you.
private Boolean needsToBeRefreshed(Connection conn, String ID) {
try {
Statement sta3 = conn.createStatement();
ResultSet rs =
sta3.executeQuery("show table status from " + database + " like '" + ID + "'");
rs.next();
Date lastmod = rs.getTimestamp("Update_time");
System.out.println("SQL " + lastmod);
sta3 = conn.createStatement();
Calendar calGMT = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
Date now = calGMT.getTime();
System.out.println("ID: " + ID);
System.out.println("Last refreshed: " + lastModifiedMap.get(ID) + " Last modified: " + lastmod);
if (lastModifiedMap.get(ID) == null || lastmod.after(lastModifiedMap.get(ID))) {
System.out.println(now);
lastModifiedMap.put(ID,now);
System.out.println("Accepted");
return true;
}
} catch (SQLException e) {
e.printStackTrace();
System.out.println("SQL exception");
System.out.println("Accepted");
return true;
}
System.out.println("Rejected");
return false;
}
}
That yields the following output:
SQL 2015-03-02 02:57:57.0
ID: Testunits
Last refreshed: null Last modified: 2015-03-02 02:57:57.0
Mon Mar 02 00:11:55 PST 2015
Accepted
Then on a second run-through:
SQL 2015-03-02 02:57:57.0
ID: Testunits
Last refreshed: Mon Mar 02 00:11:55 PST 2015 Last modified: 2015-03-02 02:57:57.0
Mon Mar 02 00:12:43 PST 2015
Accepted
I have been scouring the web and recognize that the Date class is not dependent on time-zones... I feel like this is a result of the SQL server being set to a different time zone and java not knowing it. So my question may be as simple as how do I get the time zone of the sql server and apply it to the date. Or if the SQL server has a non time-zone specific timestamp for last modified...
Any help would be appreciated. Thanks!
Edit:
After comments I have changed my code to create my "now" time as a Calendar and have changed the output to display the time values. See edit above for new initialization. New output is below:
SQL 2015-03-02 02:57:58.0
ID: Testunits
Last refreshed: 1425286285712 Last modified: 1425293878000
Mon Mar 02 00:51:30 PST 2015
Accepted
As far as I can tell, my "now" time has to be correct, independent of time zones, but the time returned by the sql server is still several hours ahead.
How can I compensate for this?
Edit 2:
Figured it out! The answer did end up helping me. Thank you.
private Boolean checkLastModified(Connection conn, String ID) {
try {
Statement sta3 = conn.createStatement();
ResultSet rs =
sta3.executeQuery("show table status from " + database + " like '" + ID + "'");
rs.next();
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
dateFormat.setTimeZone(TimeZone.getTimeZone("EST"));
Date lastmod = dateFormat.parse(rs.getString("Update_time"));
sta3 = conn.createStatement();
Calendar calGMT = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
Date now = calGMT.getTime();
if (lastModifiedMap.get(ID) == null || lastmod.after(lastModifiedMap.get(ID))) {
System.out.println(now);
lastModifiedMap.put(ID,now);
return true;
}
} catch (SQLException e) {
System.out.println("SQL exception");
return true;
} catch (ParseException e) {
System.out.println("Parse exception");
return true;
}
return false;
}
}