MySQL Bugs: #71168: Not getting proper results with Datetime column when using date IST format

Bug #71168	Not getting proper results with Datetime column when using date IST format
Submitted:	18 Dec 2013 6:37	Modified:	13 Jan 2014 14:39
Reporter:	syam gadde	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.6.14	OS:	Any
Assigned to:	Alexander Soklakov	CPU Architecture:	Any

Description:
In new Version If I tried to fetch the Datetime values with out formatting the java util Date we are not getting the proper results with Equals(=) condition.

Ex:I inserted new Java Util date and it was taking YYYY-MM-dd HH:mm:ss format by default but while retrieving the same row by using Java Util date without format(Wed Dec 18 10:55:42 IST 2013) it is not returning that particular record and date between also not returning the proper results.It is missing equals records.

How to repeat:
Insert a Java util current date into DateTime column 
Date dt = new Date();
Calendar cal = Calendar.getInstance();  
cal.add(Calendar.DATE, -1);  
Date yesterday =  cal.getTime();

Insert and retrieve the same current date without formatting,then we don't get any results even though there a record with same date.

Thank you for the report.

You retrieve the date as:

ResultSet rs1 = preparedStatement.executeQuery();
 
			while (rs1.next()) {
				String id = rs1.getString("DateCreated");
				System.out.println("Curr date : " + id);
			}

Why do you expect that rs1.getString("DateCreated") will return value not in MySQL default format, but in Date default format?

Hi Syam,

This isn't a bug actually but the side effect of fractional seconds support introduced in 5.6 server. If you don't specify format then driver converts date to string with milliseconds part, you can ensure this through general log:

		   10 Query	DROP TABLE IF EXISTS testBug71168
		   10 Query	CREATE  TABLE testBug71168 (ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, DateCreated DATETIME, DateModified DATETIME, CreatedBy VARCHAR(32), DateEntered DATETIME)
		   10 Query	insert into testBug71168 (DateCreated,DateModified,CreatedBy,DateEntered) values ('2014-01-13 17:11:22.705','2014-01-13 17:11:22.705','user','2014-01-13')
		   10 Query	SELECT ID FROM testBug71168 WHERE DateCreated between '2014-01-12 17:11:22.71' and '2014-01-13 17:11:22.705'
		   10 Query	SELECT * FROM testBug71168 WHERE DateCreated = '2014-01-13 17:11:22.705'
		   10 Query	DROP TABLE IF EXISTS testBug71168

In this case values stored to DB loose fractional part thus you get the result:

Connected to 5.6.11-log
curr date--Mon Jan 13 17:11:22 MSK 2014
sqlDate date--2014-01-13
curr calendat date--Sun Jan 12 17:11:22 MSK 2014
insert :1
select Between
select Qry

This problem can be solved by using formatted string or you can add fractional part to your datetime fields definitions:

		   21 Query	DROP TABLE IF EXISTS testBug71168
		   21 Query	CREATE  TABLE testBug71168 (ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, DateCreated DATETIME(3), DateModified DATETIME(3), CreatedBy VARCHAR(32), DateEntered DATETIME(3))
		   21 Query	insert into testBug71168 (DateCreated,DateModified,CreatedBy,DateEntered) values ('2014-01-13 17:34:48.371','2014-01-13 17:34:48.371','user','2014-01-13')
		   21 Query	SELECT ID FROM testBug71168 WHERE DateCreated between '2014-01-12 17:34:48.377' and '2014-01-13 17:34:48.371'
		   21 Query	SELECT * FROM testBug71168 WHERE DateCreated = '2014-01-13 17:34:48.371'
		   21 Query	DROP TABLE IF EXISTS testBug71168

so that they are compared now using the same precision:

curr date--Mon Jan 13 17:34:48 MSK 2014
sqlDate date--2014-01-13
curr calendat date--Sun Jan 12 17:34:48 MSK 2014
insert :1
select Between
IDDDDDDDDD : 1
select Qry
Curr date : 2014-01-13 17:34:48.371

I close your report as Not a Bug, please feel free to reopen it if you find something wrong here.