MySQL Bugs: #84059: Driver is stripping off fractional part of TIME when calling ResultSet.getObject

Bug #84059	Driver is stripping off fractional part of TIME when calling ResultSet.getObject
Submitted:	5 Dec 2016 11:26	Modified:	27 Dec 2016 8:56
Reporter:	kriti suwalka	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	6.0.5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I have a TIME column with fractional seconds in my table as below
 
create table datetime_edit(
time_3 time(3),
datetime_6 datetime(6),
date_ date
);

I inserted a TIME value which has fractional seconds in it as below

insert into datetime_edit values('12:09:08.123','1996-09-10 12:09:08.123456','2014-11-10');

It gives correct result on executing "select * from datetime_edit" from mysql workbench. But it strips off fractional seconds from time field if I call getObject() on resultset.

How to repeat:
1) create a table in mysql db.
create table datetime_edit(
time_3 time(3),
datetime_6 datetime(6),
date_ date
);

2) create a connection to mysql db.

try {
Connection connection = DriverManager.getConnection(
				"jdbc:mysql://<host><port>/<database name>",                       "<user>","<password>");
   } 
catch (SQLException e) {
      System.out.println("Connection Failed!");
      e.printStackTrace();
      return;
}
3) Below is the piece of code which will show the same behaviour as reported.

try {
	String query = "select * from datetime_edit";
	PreparedStatement stmt = connection.prepareStatement(query);
        ResultSet rs = stmt.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
	int columns = rsmd.getColumnCount();
	System.out.println("query executed successfully");
        while(rs.next()) {
         for (int col=0; col < columns; col++) {
	   Object o = rs.getObject(col+1);
	   System.out.println("Column" + col +":  NAME-  " + rsmd.getColumnName(col+1) + "    TYPE-  " + rsmd.getColumnTypeName(col+1) +"  VALUE-  "+ o);
         }
       }
     }
     catch (Exception e) {
			e.printStackTrace();
			return;
    }

Below is the output received on executing this piece of code.

query executed successfully
Column0:  NAME-  time_3    TYPE-  TIME  VALUE-  12:09:08
Column1:  NAME-  datetime_6    TYPE-  DATETIME  VALUE-  1996-09-10 12:09:08.123456
Column2:  NAME-  date_    TYPE-  DATE  VALUE-  2014-11-10

NOTE: Expected TIME VALUE is 12:09:08.123 but it gives 12:09:08.

Hello kriti suwalka,

Thank you for the bug report.
The result of rs.getObject(col+1) is returning a java.sql.Time#toString() instance, so when you going to prints it, it just gets the toString() of it and it doesn't mean the milliseconds value isn't there, it just doesn't show. To get those values have to use a custom DateFormat  like SimpleDateFormat("HH:mm:ss.SSS").

Thanks,
Chiranjeevi.

Thanks for your response :)
I am using below code now

String pattern = "HH:mm:ss.SSS";
SimpleDateFormat simpledate = new SimpleDateFormat(pattern);
simpledate = (SimpleDateFormat) rs.getObject(col+1);

But it is throwing exception "java.sql.Time cannot be cast to java.text.SimpleDateFormat".

As per my requirement we need to read this time field from our source table and insert the value in our target table. But when we read it using getObject(), it does not read fractional part hence the value inserted in target table too does not have fractional part. It would be great if you could tell us how can we read time field so it does not truncate fractional part. Please note that in case of datetime field, it reads as expected. I wonder why it is not reading properly in case of time field.

Hello kriti suwalka,

Thank you for the bug feedback.
I could not repeat the issue at our end using with Connector/Java 6.0.5 version.

String strDate  = new SimpleDateFormat("HH:mm:ss.SSS").format(rs.getTime(col+1));  
System.out.println(strDate);

This doesn't work with C/J 5.1 due to legacy limitations, but you can still do the following to achieve the same result

String strDate  = new SimpleDateFormat("HH:mm:ss.SSS").format(rs.getTimestamp(col+1));  
System.out.println(strDate);

Thanks,
Chiranjeevi.

Thanks Chiranjeevi :)

Thank you for the bug feedback.