[#CONJ-810] ResultSet.getDate() return value inconsistent between MySQL and MariaDB connectors

[CONJ-810] ResultSet.getDate() return value inconsistent between MySQL and MariaDB connectors Created: 2020-07-22 Updated: 2020-09-24 Resolved: 2020-09-23
Status:	Closed
Project:	MariaDB Connector/J
Component/s:	JDBC compatibility
Affects Version/s:	2.6.1
Fix Version/s:	2.7.0

Type:

Bug

Priority:

Major

Reporter:

Bibhas Sarkar

Assignee:

Diego Dupin

Resolution:

Fixed

Votes:

Labels:

None

Attachments:

Sample.java

Description

The getDate() method of the ResultSet returns java.sql.Date however the underlying GregorianDate has the time component. This results in failure of date comparison if assumed that the date always has time set as 00 hours.

A simple test between MySQL and MariaDB yields different results

/** Code Example **/
Properties credentials = new Properties();
credentials.setProperty("user", "abs");
credentials.setProperty("password", "xxxxxxx");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MMM-dd HH:mm:ss");

//MySQL Driver
{
try(Connection con = new com.mysql.jdbc.Driver().connect("jdbc:mysql://localhost:3310/safenet?useUnicode=true&characterEncoding=UTF-8&jdbcCompliantTruncation=false&zeroDateTimeBehavior=convertToNull", credentials))
{
try(Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("select now()"))
{
if (rs.next())

{ System.out.printf("MySQL Date Returned: %s\n", sdf.format(rs.getDate(1))); }

}
}
catch (SQLException e)

{ e.printStackTrace(); }
}

//MariaDB Driver
{
try(Connection con = new org.mariadb.jdbc.Driver().connect("jdbc:mariadb://localhost:3308/safenet?useUnicode=true&characterEncoding=UTF-8&jdbcCompliantTruncation=false&zeroDateTimeBehavior=convertToNull", credentials))
{
try(Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("select now()"))
{
if (rs.next())
{ System.out.printf("MariaDB Date Returned: %s\n", sdf.format(rs.getDate(1))); }
}
}
catch (SQLException e)
{ e.printStackTrace(); }

}

/** OUTPUT **/

MySQL Date Returned: 2020-Jul-22 00:00:00
MariaDB Date Returned: 2020-Jul-22 16:42:32

===================================================
Drivers Used
===================================================

<dependency>
<groupId>org.mariadb.jdbc</groupId>
<artifactId>mariadb-java-client</artifactId>
<version>2.6.1</version>
</dependency>

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.49</version>
</dependency>

Comments

Comment by Diego Dupin [ 2020-09-23 ]

This concerns the normalization of resultset getDate/getTime of timestamp field.

as indicated in javadoc (Time and Date)

To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance is now 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated (current client timezone or if option `useLegacyDatetimeCode` is disabled to server timezone).

Same for java.sql.Time:

The date components is set to the "zero epoch" value of January 1, 1970.

example: getTime() on value '2015-03-29T01:45:00.012' return a java.sql.Time with value '01:45:00', submethod getTime return time in millisecond that did correspond to '2015-03-29T01:45:00.012'. Now correspond to '1970-01-01T01:45:00.012'

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

[CONJ-810] ResultSet.getDate() return value inconsistent between MySQL and MariaDB connectors Created: 2020-07-22 Updated: 2020-09-24 Resolved: 2020-09-23