SQL DATEs stored wrong if client timezone is east of server timezone

The SQL DATE type is just plain year/month/day (without hours/minutes/etc), and in MariaDB, it does not include a time zone.

Thus, writing a java.sql.Date to a DATE column (or reading a DATE column to java.sql.Date) should not depend on client or server time zones. Time zone differences are expressed in hours, and you can't really add or subtract one hour to a plain date that does not have an hours part.

(Note that the situation for writing a java.util.Date to a DATE column might be different, and might sensibly depend on some time zones.)

The current behavior causes wrong behavior when the server timezone is west of the client timezone. The test program below (when server is UTC and client is Europe/Helsinki) attempts to store 2013-11-20 to the database, but reads back 2013-11-19.

class MariaDateTest {

   public static void main(String[] args) throws Exception {

       String url = "jdbc:mariadb://127.0.0.1:3306/peti?serverTimezone=UTC";

       String userName = "root";

       String userPassword = "";

       Connection conn = DriverManager.getConnection(url, userName, userPassword);

       Statement st = conn.createStatement();

       st.execute("drop table if exists date_test");

       st.execute("create table date_test ( x date )");

       PreparedStatement pst = conn.prepareStatement("insert into date_test values (?)");

       java.sql.Date date = java.sql.Date.valueOf("2013-11-20");

       System.out.println("Storing java.sql.Date "+date);

       pst.setDate(1, date);

       pst.execute();

       ResultSet rs = st.executeQuery("select x from date_test");

       rs.next();

       System.out.println("Got back java.sql.Date: " + rs.getDate(1));

   }

}

Proposed fix:

• In DateParameter.java, change "java.util.Date" to "java.sql.Date" and the comment to "Represents a SQL DATE (date without time)" (it's currently used only for java.sql.Dates anyway)
• In ParameterWriter.java, change the method simply to:

   public static void writeDate(OutputStream out, java.sql.Date date, Calendar calendar) throws IOException {

       out.write(QUOTE);

       out.write(date.toString()); // guaranteed to be in YYYY-MM-DD format

       out.write(QUOTE);

    }

• In MySQLPreparedStatement.java, add a comment in setDate() to something like this: "Since MariaDB does not store a time zone with SQL DATEs, and SQL DATEs do not include a time part, no timezone calculations are done, and the <code>Calendar</code> object is not used by the MariaDB Java Client."

BTW, the MySQL Connector/J driver has a similar bug, but that's trickier to fix since there's shared code between java.sql.Dates (without time) and java.util.Dates (with time). See http://bugs.mysql.com/bug.php?id=71084