I'm using Hibernate and MySQL and I'm having an inconsistency problem related to storing, retrieving and comparing dates.

I have a table called ProfileVisit which has a date field. The hibernate mapping is:
<property name="visitDate" column="Visit_Date" type="timestamp" not-null="true"/>
This maps to a DATETIME column in MySQL.

This is a scenarion of the problem:
1) I persist a ProfileVisit pv1 with visitDate = T0.
2) I execute a HQL query "FROM ProfileVisit visit WHERE visit.visitDate <= :date" where the ":date" param is set to a value T1 > T0.
3) the previously persisted pv1 won't be retrieved unless T1 is greater than T0 with at least 11 hours.

My timezone is GMT+2 and I'm using MySQL 5.1.6 and Hibernate 3.0. I suppose this is something related to MySQL storing the dates into its own timezone or something related to Hibernate's Query.setDate() method but I can't figure it out.

If you've read so far..... thank you.

What method are you using to assign the date parameter for the query?

Hibernate could be stripping off the time aspect if you are using setDate instead of setTimestamp.

Do you need the precision to go down to hours, minutes and seconds, or could you get by with just the date?

--
Stephen Souness

Hi,

I'm using the setDate(String name, Date date) method and I need the precision to go down to milliseconds. In the Query API all methods: setDate, setTime and setTimestamp receive as a second param a java.util.Date. Perhaps you're right. I'll try using setTimestamp.

Many thanks.

Just wanted to say that using Query.setTimestamp(String, Date) did the trick. It seems that Sounie was right, namely Query.setDate(String, Date) sets only the date component on the parameter even if the date contains a time component. Thanks again Sounie.