How to compare objects with a Date variable (Hibernate)? java.util.Date vs java.sql.Timestamp

Question

Environment:

MySql 5.6.14
Spring MVC
Hibernate

I'm having problems to check if 2 objects are equal, and the problem is caused by de variable Date.

I'm using Hibernate and it seems to be the problem of Timestamp (nanoseconds) and Date(miliseconds).

When I create the object Task date is different that when I retreived the object from database.

java.util.Date date = new java.util.Date();
java.sql.Date datesql = new java.sql.Date(date.getTime());
java.util.Date stamp = new java.sql.Timestamp(date.getTime());
Date rightnow = stamp;//Calendar.getInstance().getTime();

Task t1 = new Task("My first task", "This is a task", rightnow,
        rightnow, category, priority, state, user, user, "okey", 0);
Task t2 = new Task("My second task", "This is a task", date,
        rightnow, category, priority, state, user, user, "okey", 0);
Task t3 = new Task("My third task", "This is a task", datesql,
        rightnow, category, priority, state, user, user, "okey", 0);
    taskDao.saveOrUpdate(t1);
taskDao.saveOrUpdate(t2);
taskDao.saveOrUpdate(t3);
taskDao.saveOrUpdate(t4);

List<Task> tasks = taskDao.getAllTasks();
assertEquals("Should be 4 tasks.", 4, tasks.size());

Task taskR1 = taskDao.get(t1.getIdTask());
Task taskR2 = taskDao.get(t2.getIdTask());
Task taskR3 = taskDao.get(t3.getIdTask());
//Dates before saving objects in database
System.out.println("l timestamp> "+rightnow.getTime());
System.out.println("l date     > "+date.getTime());
System.out.println("l datesql  > "+datesql.getTime());
//Dates from retrieved objects
System.out.println("l tr1date  > "+taskR1.getDate().getTime());
System.out.println("l tr2date  > "+taskR2.getDate().getTime());
System.out.println("l tr3date  > "+taskR3.getDate().getTime());

Results of this code

//Dates before saving objects in database
l timestamp> 1401298705937
l date     > 1401298705937
l datesql  > 1401298705937
//Dates from retrieved objects
l tr1date  > 1401298706000
l tr2date  > 1401298706000
l tr3date  > 1401298706000

The problem is that when compare 1401298705937 (Date before saving object) and 1401298706000(Date after saving object) are different.

I've also tried with compareTo and equals functions

System.out.println("t3.getDate().compareTo(taskR3.getDate()) == 0 :" 
  + (t3.getDate().compareTo(taskR3.getDate()) == 0));
System.out.println("t3.getDate().equal(taskR3.getDate()) == 0 :" 
  + (t3.getDate().compareTo(taskR3.getDate()) == 0));
System.out.println("taskR3.getDate().compareTo(t3.getDate()) == 0 :" 
  + (t3.getDate().compareTo(taskR3.getDate()) == 0));
System.out.println("taskR3.getDate().equal(t3.getDate()) == 0 :" 
  + (t3.getDate().compareTo(taskR3.getDate()) == 0));

And result is false, as well with objects t1, t2 and taskR1, taskR2.

t3.getDate().compareTo(taskR3.getDate()) == 0 :false
t3.getDate().equal(taskR3.getDate()) == 0 :false
taskR3.getDate().compareTo(t3.getDate()) == 0 :false
taskR3.getDate().equal(t3.getDate()) == 0 :false

Any ideas?

t3 date Wed May 28 19:38:25 CEST 2014 and tR3 date 2014-05-28 19:38:26.0 — Joe
– Joe, Commented May 28, 2014 at 21:17

Vlad Mihalcea · Accepted Answer · 2014-05-28 20:28:29Z

1

From you example:

1401298705937 = Wed, 28 May 2014 17:38:25 GMT 1401298706000 = Wed, 28 May 2014 17:38:26 GMT

Meaning the DB simply strips millis.

According to Hibernate docs:

java.util.Date
java.sql.Timestamp
java.util.Calendar

are all mapped to JDBC TIMESTAMP.

From now on it's the database whose responsible of storing the timestamp and not all versions are able to handle milliseconds.

PostgreSQL has microsecond precision.
MySQL 5.6 has just recently added fractional seconds support, while previous versions stored the timestamps without millis:

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

Is it that you use MySQL 5.5 or lower?

answered May 28, 2014 at 20:28

Vlad Mihalcea

156k85 gold badges599 silver badges984 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Joe Over a year ago

Actually, I'm using MySql 5.6.14

Vlad Mihalcea Over a year ago

Check the actual column types in DB, and try running a manual insert having a timestamp containing millis. If that works, you can try adding the same entry with JDBC and see if it works. This way you can isolate the problem better.

Vlad Mihalcea Over a year ago

Also you might be interested in trying the proposed extended dialect of this SO question.

Joe Over a year ago

This must be an Bug in MySQL bugs.mysql.com/bug.php?id=27838, although I'm using MySQL 5.6

Vlad Mihalcea Over a year ago

But that's an old issue from 2007, it was not filled for the latest version.

|

Joe · Accepted Answer · 2014-05-30 09:26:59Z

0

Before that Date is inserted into MySQL with Hibernate

 1401298705937 = Wed, 28 May 2014 17:38:25 GMT

After that Date is recovered from MySQL with Hibernate

 1401298706000 = Wed, 28 May 2014 17:38:26 GMT

Date value has been rounded from 1401298705937(milis) to 1401298706000(milis) and that is why the objects aren't compareTo()==0

The solution I've found, it is to rule out milis before set Date or Timestamp and saving the object with hibernate.

new java.util.Date((date.getTime()/1000)*1000)

edited May 30, 2014 at 9:26

answered May 30, 2014 at 9:16

Joe

7,93920 gold badges70 silver badges115 bronze badges

Collectives™ on Stack Overflow

How to compare objects with a Date variable (Hibernate)? java.util.Date vs java.sql.Timestamp

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related