Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Comparing Dates with HQL

 
Corey McGlone
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So I'm trying to run an HQL query which should compare two date values and it's not behaving in the way I'd expect it to. In short, it compares the date values, but seems to ignore the hours, minutes, and seconds.

Here's my query:



The method "getRolloffDate()" gets a date in the past.

To test this, I put a record in the database with an activationDate of 14 days and 10 minutes ago. I then set getRolloffDate() to get me a date of 14 days ago. The record in the database therefore has an activationDate 10 minutes prior to the date I'm comparing it to.

While I expected this record to be excluded from the query results, it is included. If I go back far enough that the date is different, the query works as expected. But, if I'm only off by a number of minutes or hours, the comparison seems to ignore that.

Any ideas?
[ June 11, 2008: Message edited by: Corey McGlone ]
 
Corey McGlone
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Seems a solution has been found...

When you use .setDate for the named parameter, as I did above, Hibernate ignores the time component of the date. If I change that to .setTimestamp, it works as I had expected it to.
 
Cameron Wallace McKenzie
author and cow tipper
Saloon Keeper
Posts: 4968
1
Hibernate Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Awesome. I was fighting with the exact same thing in parallel to you!

-Cameron McKenzie
 
Tim Holloway
Saloon Keeper
Posts: 18367
56
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dates are treacherous things in ORM. There are at least 3 different types:

1. java.util.Date, precise to milliseconds

2. java.sql.Date, precise to days

3. Oracle dates (mapped to one of the above) precise to seconds.

The problem here is that java.sql.Date is a subclass of java.util.Date, and there's absolutely no warning when precision is lost. Your in-memory object can be just fine, but once persisted, the actual database column value may not be exactly equal to that value. Conversely, it's possible to set a java.sql.Date with a non-zero hour/minute/second/millisecond value even though officially it's only precise to 24 hours.

So if you use a date field as search criteria, it can fail for no apparent reason. It's like the problem with floating-point, only worse.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic