1

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;
}
}
Peter F
  • 435
  • 1
  • 8
  • 17
  • 1
    possible duplicate of [How to change TIMEZONE for a java.util.Calendar/Date](http://stackoverflow.com/questions/13470830/how-to-change-timezone-for-a-java-util-calendar-date) – Scary Wombat Mar 02 '15 at 08:22
  • 1
    Note that timestamps don't have an intrinsic timezone. They are simply a measure of the elapsed time since a datum, e.g. 1970-1-1 00:00:00 UTC - which is defined in terms of a timezone, but that simply specifies a unique instant in time. If you compare by timestamp, and both computers' times are set correctly, the timezone should not be an issue. – Andy Turner Mar 02 '15 at 08:24

0 Answers0