[CONJ-94] SQL DATEs stored wrong if client timezone is east of server timezone - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 1.1.7
Fix Version/s: 1.3.0
Component/s: Other
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

Attachments

Activity

Ascending order - Click to sort in descending order

Pasi Eronen created issue - 2014-04-24 14:25

Pasi Eronen made changes - 2014-04-24 14:26

Field	Original Value	New Value
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	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. {noformat} 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)); } } {noformat} 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: {noformat} 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); } {noformat} - 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

Rasmus Johansson (Inactive) made changes - 2014-06-19 15:16

Workflow

defaullt [ 39808 ]

MariaDB v2 [ 47803 ]

Massimo Siani (Inactive) made changes - 2014-07-11 11:55

Assignee

Georg Richter [ georg ]

Massimo Siani [ massimo.siani ]

Massimo Siani (Inactive) made changes - 2014-07-11 11:55

Status

Open [ 1 ]

In Progress [ 3 ]

Massimo Siani (Inactive) made changes - 2014-07-11 13:01

Status

In Progress [ 3 ]

In Review [ 10002 ]

Rasmus Johansson (Inactive) made changes - 2014-09-17 14:43

Fix Version/s

1.1.8 [ 15700 ]

Rasmus Johansson (Inactive) made changes - 2014-09-22 17:48

Workflow

MariaDB v2 [ 47803 ]

MariaDB connectors [ 54903 ]

Rasmus Johansson (Inactive) made changes - 2015-02-16 22:52

Fix Version/s		1.1.9 [ 16400 ]
Fix Version/s	1.1.8 [ 15700 ]

Rasmus Johansson (Inactive) added a comment - 2015-06-05 13:48

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

Rasmus Johansson (Inactive) added a comment - 2015-06-05 13:48 Please verify if this bug still exists (I think it does) and try to fix

Rasmus Johansson (Inactive) made changes - 2015-06-05 13:48

Assignee

Massimo Siani [ massimo.siani ]

diego dupin [ diego dupin ]

Rasmus Johansson (Inactive) made changes - 2015-06-05 13:48

Fix Version/s		1.1.10 [ 19403 ]
Fix Version/s	1.1.9 [ 16400 ]

Rasmus Johansson (Inactive) made changes - 2015-06-23 14:15

Workflow

MariaDB connectors [ 54903 ]

MariaDB v3 [ 70173 ]

Diego Dupin made changes - 2015-07-21 11:10

Fix Version/s		1.2.1 [ 19602 ]
Fix Version/s	1.2.0 [ 19403 ]

Diego Dupin made changes - 2015-08-06 18:46

Sprint

Sprint 1 [ 11 ]

Diego Dupin made changes - 2015-08-06 18:46

Rank

Ranked higher

Diego Dupin added a comment - 2015-09-18 16:13

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);

Diego Dupin added a comment - 2015-09-18 16:13 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); }

Diego Dupin made changes - 2015-09-18 16:14

Component/s		Other [ 12201 ]
Resolution		Fixed [ 1 ]
Status	In Review [ 10002 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:27

Workflow

MariaDB v3 [ 70173 ]

MariaDB v4 [ 134724 ]

People

Assignee:: Diego Dupin

Reporter:: Pasi Eronen

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2014-04-24 14:25

Updated:: 2015-09-18 16:14

Resolved:: 2015-09-18 16:14

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Connector/J

Details

Description

Attachments

Activity

People

Dates

Git Integration