[CONJ-94] SQL DATEs stored wrong if client timezone is east of server timezone Created: 2014-04-24  Updated: 2015-09-18  Resolved: 2015-09-18

Status: Closed
Project: MariaDB Connector/J
Component/s: Other
Affects Version/s: 1.1.7
Fix Version/s: 1.3.0

Type: Bug Priority: Major
Reporter: Pasi Eronen Assignee: Diego Dupin
Resolution: Fixed Votes: 0
Labels: None

Sprint: Sprint connector/j 1.3.0

 Description   

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

 Comments   
Comment by Rasmus Johansson (Inactive) [ 2015-06-05 ]

Please verify if this bug still exists (I think it does) and try to fix

Comment by Diego Dupin [ 2015-09-18 ]

reproduced, and corrected in the next version 1.3.0.
done like the proposal for not binary query: 

        out.write(QUOTE);
 
        out.write(date.toString().getBytes());
 
        out.write(QUOTE);

and in binary format (for preparedStatement) other way (using calendar was 20 time less performant )

buffer.put((byte) 7);//length
 
        String dt = date.toString(); //"yyyy-mm-dd"
 
        buffer.putShort(Short.parseShort(dt.substring(0, 4)));
 
        buffer.put(Byte.parseByte(dt.substring(5, 7)));
 
        buffer.put(Byte.parseByte(dt.substring(8, 10)));
 
        buffer.put((byte) 0);
 
        buffer.put((byte) 0);
 
        buffer.put((byte) 0);

retreiving data is using a deprecated method that will have to wait end of life of java 7 to permit use java 8 yoda time implementation.

return new Date(
 
                            Integer.parseInt(rawValue.substring(0, 4))  - 1900,
 
                            Integer.parseInt(rawValue.substring(5, 7))  - 1,
 
                            Integer.parseInt(rawValue.substring(8, 10))
 
                            );

tests added( for server and client query)

 
 
    @Test
 
    public void dateTestWhenServerDifference() throws Throwable {
 
        setConnection("&serverTimezone=UTC");
 
 
 
        Statement st = connection.createStatement();
 
        st.execute("drop table if exists date_test");
 
        st.execute("create table date_test ( x date )");
 
 
 
        PreparedStatement pst = connection.prepareStatement("insert into date_test values (?)");
 
        java.sql.Date date = java.sql.Date.valueOf("2013-02-01");
 
        pst.setDate(1, date);
 
        pst.execute();
 
 
 
        pst = connection.prepareStatement("select x from date_test WHERE x = ?");
 
        pst.setDate(1, date);
 
        ResultSet rs = pst.executeQuery();
 
        rs.next();
 
        Date dd = rs.getDate(1);
 
        assertEquals(dd, date);
 
    }
 
 
 
 
 
    @Test
 
    public void dateTestWhenServerDifferenceClient() throws Throwable {
 
        setConnection("&serverTimezone=UTC");
 
 
 
        Statement st = connection.createStatement();
 
        st.execute("drop table if exists date_test");
 
        st.execute("create table date_test ( x date )");
 
 
 
        PreparedStatement pst = connection.prepareStatement("/*CLIENT*/insert into date_test values (?)");
 
        java.sql.Date date = java.sql.Date.valueOf("2013-02-01");
 
        pst.setDate(1, date);
 
        pst.execute();
 
 
 
        pst = connection.prepareStatement("/*CLIENT*/ select x from date_test WHERE x = ?");
 
        pst.setDate(1, date);
 
        ResultSet rs = pst.executeQuery();
 
        rs.next();
 
        Date dd = rs.getDate(1);
 
        assertEquals(dd, date);
 
    }

Generated at Thu Feb 08 03:13:07 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.