Why does mybatis insert java.util.Date value into mysql datetime column and then retrieve it, are they not equal?

Question

Mybatis insert java.util.Date into the mysql datetime column, then select it if they don't match.

Table

CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `create_time` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 

code

 @Insert("insert into t(create_time) values(#{0})") int insertT(Date date); @Select("select create_time from t order by id desc limit 1") Date getLatestCreateTime(); 

Unit test

  Date date1 = new Date(); mapper.insertT(date1); Date date2 = mapper.getLatestCreateTime(); Assert.assertEquals(date1, date2); 

Confirmation Error

 java.lang.AssertionError: Expected :Tue Aug 02 22:10:35 CST 2016 Actual :Tue Aug 02 22:10:36 CST 2016 

why is that?

Answer 1

because when TimeUtil in mysql-connector converts Timstamp to Date, it runs below code

  Timestamp ts = new Timestamp(tsAsMillis1 + (long)offsetDiff); ts.setNanos(secondsPart); 

and secondsPart is 0.

 @Test public void test_date_compare_date_from_timestamp() { Date date1 = new Date(); Timestamp ts = new Timestamp(date1.getTime()); System.out.println(ts); // 2016-08-08 22:37:37.078 Date date2 = new Date(ts.getTime()); Assert.assertEquals(date1, date2); ts.setNanos(0); System.out.println(ts); // 2016-08-08 22:37:37.0 Date date3 = new Date(ts.getTime()); Assert.assertNotEquals(date1, date3); } 

and for seconds more than the original seconds, this is because

 insert into t select 1,'2016-08-08 22:42:44.676'; select * from t; +----+---------------------+ | id | create_time | +----+---------------------+ | 1 | 2016-08-08 22:42:45 | +----+---------------------+