When I enter an old date (e.g., 20/03/1961 (dd/mm/yyyy) the value one day earlier is stored

Question

I am using MySQL, Eclipse, GWT java and when I enter an old date 20/03/1961 (dd/mm/yyyy) the value stored is 19/03/1961. I get the same result irrespective of whether I manually input the date or use the date picker (i.e., it displays as 20/03/1961 on input but when I redisplay it displays as 19/03/1961). I have checked the DB (MySQL) and it is stored as 1961-03-19. In the DB the column is defined as date and collation is utf8_general_ci.

The error seems to only occur between certain date ranges. On the 12/12/2014 @ 11:24 I tested by halving dates back and I found the error on 4/4/1971, i.e., 5/4/1971 was stored correctly and 4/4/1971 was stored as 3/4/1971. I then entered the original date I had trouble with, 31/3/2004 and the error occurred (i.e., it was stored as 30/3/2004). I have been trying various other dates and found 3/4/2007 is stored as 2/4/2007 while 4/4/2007 is OK. a mathematical genius could probably find a pattern to this.

The relevant code is:

Client side:

final DateBox dateBoxDOB = new DateBox();

//dateBoxDOB = new DateBox();
dateBoxDOB.setFormat(new DefaultFormat(DateTimeFormat.getFormat("dd/MM/yyyy")));
flexTable.setWidget(0, 1, dateBoxDOB);
dateBoxDOB.getDatePicker();

java.sql.Date sqlDOB = new java.sql.Date(dateBoxDOB.getValue().getTime());

AsyncCallback<Void> callback = new UpdateHandler<Void>(EditYouthMemberView.this);
                rpc.updateYouthMember(cd_ID, textBoxSurname.getText(), textBoxFirstName.getText(), sqlDOB, 
                    imagePath, sqlDateArchived, integerBoxScoutNumber.getValue(), sd_ID, "Cubs",
                    "Explorer", sqlPackIn, sqlDatePackOut, callback);

Server side:

public void updateYouthMember(String cd_id, String surname,
    String firstname, java.sql.Date dob, String photograph, java.sql.Date archived,
    Integer scout_no, String sd_id, String section, String pack, java.sql.Date startDate,
    java.sql.Date endDate) {

PreparedStatement ps = null;
PreparedStatement ps2 = null;
// Create connection/statement variables outside of try block
Connection c = null;

String updateWithoutPhoto = ("UPDATE at_cub_details " +
        "SET cd_surname = ?, " +
        "cd_first_name = ?, " +
        "cd_dob = ?, " +
        "cd_archived = ?, " +
        "cd_scout_no = ? " +
        "WHERE cd_id = ?;");

String updateWithPhoto = ("UPDATE at_cub_details " +
        "SET cd_surname = ?, " +
        "cd_first_name = ?, " +
        "cd_dob = ?, " +
        "cd_photograph = ?, " +
        "cd_archived = ?, " +
        "cd_scout_no = ? " +
        "WHERE cd_id = ?;");

try {
    // Get Connection and Statement from DataSource
    c = ds.getConnection();

    try {
        if (photograph == null) {
            // Create a statement and execute the query on it
            ps = c.prepareStatement(updateWithoutPhoto);
            ps.setString(1, surname);
            ps.setString(2, firstname);
            ps.setDate(3, (java.sql.Date) dob);
            ps.setDate(4, (java.sql.Date) archived);
            ps.setInt(5, scout_no);
            ps.setString(6, cd_id);

            ps.executeUpdate();

            // Clean up
            ps.close();
        }else{
            // Create a statement and execute the query on it
            ps = c.prepareStatement(updateWithPhoto);
            ps.setString(1, surname);
            ps.setString(2, firstname);
            ps.setDate(3, (java.sql.Date) dob);

            File imgfile = new File (photograph);
            FileInputStream fis = new FileInputStream(imgfile);
            ps.setBinaryStream(4, fis, (int) imgfile.length());

            ps.setDate(5, (java.sql.Date) archived);
            ps.setInt(6, scout_no);
            ps.setString(7, cd_id);

            ps.executeUpdate();

            // Clean up
            ps.close();
        }
    } catch (SQLException se) {
        System.out.println("SQLException in updateYouthMember: " + se.toString());
    } catch (Exception e) {
        System.out.println("Errors occurred in updateYouthMember: " + e.toString());
    }

String updateSectionDetails = ("UPDATE at_section_details " +
        "SET sd_section = ?, " +
        "sd_pack = ?, " +
        "sd_start_date = ?, " +
        "sd_end_date = ? " +
        "WHERE sd_id = ?;");
try {
    // Create a statement and execute the query on it
    ps2 = c.prepareStatement(updateSectionDetails);
    ps2.setString(1, section);
    ps2.setString(2, pack);
    ps2.setDate(3, (java.sql.Date) startDate);
    ps2.setDate(4, (java.sql.Date) endDate);
    ps2.setString(5, sd_id);

    ps2.executeUpdate();

    // Clean up
    ps2.close();
    c.close();

} catch (SQLException se) {
    System.out.println("SQLException in updateYouthMember - updateSectionDetails: " + se.toString());
} catch (Exception e) {
    System.out.println("Errors occurred in updateYouthMember - updateSectionDetails: " + e.toString());
}

} catch (SQLException e1) {
    System.out.println("SQLException in updateYouthMember: " + e1.toString());
    e1.printStackTrace();

} 

finally {

// Ensure connection is closed and returned to the pool, even if errors occur.
// This is *very* important if using a connection pool, because after all the
// connections are used, the application will hang on getConnection(), waiting
// for a connection to become available.
// Any errors from the following closes are just ignored.  The main thing is
// that we have definitely closed the connection.
try { if(ps != null) ps.close(); } catch (Exception e) {}
try { if(c != null) c.close(); } catch (Exception e) {}
}
// Done
}

I have already tried:

I found this: bugs.mysql.com/bug.php?id=71084. However, when I use ps.setDate(3, (java.sql.Date) dob, java.util.Calendar.getInstance()); I still get 19/03/1961 when I enter 20/03/1961.

I tried:

// create a calendar Locale locale1 = Locale.UK; 
TimeZone tz1 = TimeZone.getTimeZone("UTC+10:00"); 
Calendar cal1 = Calendar.getInstance(tz1, locale1); 
ps.setDate(3, (java.sql.Date) dob, cal1); 

and this did not work.

I also tried TimeZone tz1 = TimeZone.getTimeZone("AEST"); which did not work either.

I then tried ps.setString(3, dob.toString()); which causes a Errors occurred in updateYouthMember: java.lang.NullPointerException.

Any help greatly appreciated.

Answer 1

This looks like a time zone issue. Here are a few pointers:

1. If you are storing the date as a String, you may want to process it as a String in GWT as well. It will save you from trouble when converting from Long to a date.

2. If you prefer to use getTime() to get the date, try to adjust this number to a midnight (see my answer to this question: how to create a Java Date object of midnight today and midnight tomorrow?)

GWT returns a point of time that is noon instead of midnight. It may enough to push the date one day back for some combination of dates and time zones.

This may not be enough, though - you may need to adjust the getTime() result by a time zone offset before converting it into a Date object, and then use the same offset the other way when showing the date in your app - the same date starts at a different time in different time zones.

3. Alternatively, you can use getTime() to get time in milliseconds, store it as time in milliseconds, and then use it as time in milliseconds as well when displaying the date. This works for things like timestamps and the like, where the time zone is irrelevant. If time zone is important, you will have to remember in which time zone to display the date - it may be the correct date in some time zones and 1 day off in the other.

I would recommend the latter two approaches only if you need exact time adjusted for a user's time zone somewhere in your app. If you only need dates, strings is a simpler solution.