Comparing the Date of a DateTime property, like MySQL DATE()

stephaneeybert · **Joined:** Mon Apr 16, 2007 8:10 am **Posts:** 246

I would like to do, with a Hibernate Criteria, something equivalent to the MySQL statement:

Code:

SELECT * FROM mail_address WHERE DATE(creation_datetime) >= '$fromDate' AND DATE(creation_datetime) <= '$toDate' ORDER BY firstname, lastname, email

I came up with the following Dao method but I get an exception when the date fromDateTime.toDate() is casted to a Joda DateTime.

Code:

   public List<MailAddress> findByCreationDateTime(DateTime fromDateTime, DateTime toDateTime) {
      Criteria criteria = getSession().createCriteria(MailAddress.class);
      // TODO how to compare between the date only and ignore the time ?
      criteria.add(Restrictions.between("creationDateTime", fromDateTime.toDate(), toDateTime.toDate()));
      criteria.addOrder(Order.asc("firstname")).addOrder(Order.asc("lastname")).addOrder(Order.asc("email"));
      return criteria.list();
   }

Code:

        <property name="creationDateTime" type="dateTime">
            <column name="creation_datetime" not-null="true" />
        </property>

Code:

   <typedef name="dateTime" class="org.joda.time.contrib.hibernate.PersistentDateTime" />

Is there any such functions in the Criteria API to consider only the Date of a DateTime property ? Something like the MySQL functions.

stephaneeybert · **Joined:** Mon Apr 16, 2007 8:10 am **Posts:** 246

I tried it with a different Criteria using a sqlRestriction as in:

Code:

   public List<MailAddress> findByCreationDateTime(DateTime fromDateTime, DateTime toDateTime) {
      Criteria criteria = getSession().createCriteria(MailAddress.class);
      // TODO how to compare between the date only and ignore the time ?
      criteria.add(Restrictions.sqlRestriction("(DATE(creationDateTime) >= ?)", fromDateTime.toDate(), Hibernate.STRING));
      criteria.add(Restrictions.sqlRestriction("(DATE(creationDateTime) <= ?)", toDateTime.toDate(), Hibernate.STRING));
      criteria.addOrder(Order.asc("firstname")).addOrder(Order.asc("lastname")).addOrder(Order.asc("email"));
      return criteria.list();
   }

But it didn't work either.

Here is the exception:

Quote:

Caused by: java.sql.SQLException: Unexpected token: DATE in statement [select this_.id as id83_0_, this_.version as version83_0_, this_.firstname as firstname83_0_, this_.lastname as lastname83_0_, this_.email as email83_0_, this_.comment as comment83_0_, this_.country as country83_0_, this_.subscribe as subscribe83_0_, this_.imported as imported83_0_, this_.creation_datetime as creation10_83_0_ from mail_address this_ where (DATE(creationDateTime) >= ?) and (DATE(creationDateTime) <= ?) order by this_.firstname asc, this_.lastname asc, this_.email asc]

stephaneeybert · **Joined:** Mon Apr 16, 2007 8:10 am **Posts:** 246

I noticed that I can run it on the command line in MySQL:

Code:

mysql> select mailaddres0_.id as id83_, mailaddres0_.version as version83_, mailaddres0_.firstname as firstname83_, mailaddres0_.lastname as lastname83_, mailaddres0_.email as email83_, mailaddres0_.comment as comment83_, mailaddres0_.country as country83_, mailaddres0_.subscribe as subscribe83_, mailaddres0_.imported as imported83_, mailaddres0_.creation_datetime as creation10_83_ from mail_address mailaddres0_ where DATE(mailaddres0_.creation_datetime)>='2010-11-01' and DATE(mailaddres0_.creation_datetime)<='2010-11-29';
Empty set (0.02 sec)

Indeed the test works fine with the following Dao method:

Code:

   public List<MailAddress> findByCreationDateTime(DateTime fromDateTime, DateTime toDateTime) {
      Query query = getSession().createQuery("from MailAddress where DATE(creationDateTime) >= ? and DATE(creationDateTime) <= ? order by firstname, lastname, email");
      query.setDate(0, fromDateTime.toDate());
      query.setDate(1, toDateTime.toDate());
      return query.list();
   }

When the test is run in a MySQL environment:

mvn clean test -Pmysql-test

But when the test is run in a HSQLDB environment:

mvn clean test -Pintegration-test

then it fails:

Quote:

Caused by: java.sql.SQLException: Unexpected token: DATE in statement [select mailaddres0_.id as id83_, mailaddres0_.version as version83_, mailaddres0_.firstname as firstname83_, mailaddres0_.lastname as lastname83_, mailaddres0_.email as email83_, mailaddres0_.comment as comment83_, mailaddres0_.country as country83_, mailaddres0_.subscribe as subscribe83_, mailaddres0_.imported as imported83_, mailaddres0_.creation_datetime as creation10_83_ from mail_address mailaddres0_ where DATE(mailaddres0_.creation_datetime)>=? and DATE(mailaddres0_.creation_datetime)<=? order by mailaddres0_.firstname, mailaddres0_.lastname, mailaddres0_.email]

I guess the Hypersonic HSQLDB does not like the DATE() function.

How can I have a statement that runs on any database environment ?

I was hoping the Criteria API would come to the rescue here...

stephaneeybert · **Joined:** Mon Apr 16, 2007 8:10 am **Posts:** 246

So I'm still stuck.

Anyone who knows how to do a comparison on the Date only of a DateTime, using criteria or other, in a Hibernate database agnostic way, I'm open.

stephaneeybert · **Joined:** Mon Apr 16, 2007 8:10 am **Posts:** 246

I tried with the following HQL query and it works, but only in MySQL and not in HSQLDB.

Code:

      Query query = getSession().createQuery("from MailAddress where DATE(creationDateTime) >= ? and DATE(creationDateTime) <= ? order by firstname, lastname, email");
      query.setDate(0, fromDateTime.toDate());
      query.setDate(1, toDateTime.toDate());
      return query.list();

I'm still looking for a cross databases solutions...

chirag.naikec · **Posted:** Tue Nov 30, 2010 6:09 am

Try this out...I m using oracle so i used TO_DATE function..You use date function according to your database.

crit.add(Expression.sql("TO_DATE("+tempFieldname+",'DD/MM/YYYY') between '"+startDate+"' and '"+endDate+"'"));

Chirag

stephaneeybert · **Joined:** Mon Apr 16, 2007 8:10 am **Posts:** 246

I tried the three following restrictions and none of them worked. The property could not be resolved.

Code:

      criteria.add(Restrictions.between("TO_DATE(creationDateTime, 'YYYY/MM/DD')", fromDateTime, toDateTime));

Code:

      criteria.add(Restrictions.between("DATE(creationDateTime, 'YYYY/MM/DD')", fromDateTime, toDateTime));

Code:

      criteria.add(Restrictions.between("DATE(creationDateTime)", fromDateTime, toDateTime));

stephaneeybert · **Joined:** Mon Apr 16, 2007 8:10 am **Posts:** 246

When trying this, I get an org.hibernate.exception.SQLGrammarException:

Code:

      criteria.add(Restrictions.sqlRestriction("(DATE(creationDateTime) >= ?)", fromDateTime.toDate(), org.hibernate.type.StandardBasicTypes.DATE));
      criteria.add(Restrictions.sqlRestriction("(DATE(creationDateTime) <= ?)", toDateTime.toDate(), org.hibernate.type.StandardBasicTypes.DATE));

chirag.naikec · **Posted:** Wed Dec 01, 2010 12:15 am

stephaneeybert wrote:

I tried the three following restrictions and none of them worked. The property could not be resolved.

Code:

      criteria.add(Restrictions.between("TO_DATE(creationDateTime, 'YYYY/MM/DD')", fromDateTime, toDateTime));

Code:

      criteria.add(Restrictions.between("DATE(creationDateTime, 'YYYY/MM/DD')", fromDateTime, toDateTime));

Code:

      criteria.add(Restrictions.between("DATE(creationDateTime)", fromDateTime, toDateTime));

This will not work because it will search for this DATE(creationDateTime, 'YYYY/MM/DD') property in bean.But it won't be present there.

Instead try the code i gave it is working fine with me.

Code:

crit.add(Expression.sql("TO_DATE("+tempFieldname+",'DD/MM/YYYY') between '"+startDate+"' and '"+endDate+"'"));

This crit.add allows us to write normal sql queries in it.

Chirag

stephaneeybert · **Joined:** Mon Apr 16, 2007 8:10 am **Posts:** 246

Thanks for bearing with me. Are you sure the TO_DATE is applied to the bean properties and not the table columns ?

I went for your HQL statement:

Code:

      criteria.add(Expression.sql("TO_DATE(creationDateTime,'DD/MM/YYYY') between '"+ fromDateTime.toDate() +"' and '"+ toDateTime.toDate() +"'"));

But, when running it against HSQLDB, it gave me an error:

Quote:

org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query

Quote:

Caused by: java.sql.SQLException: Unexpected token: TO_DATE in statement [select this_.id as id83_0_, this_.version as version83_0_, this_.firstname as firstname83_0_, this_.lastname as lastname83_0_, this_.email as email83_0_, this_.comment as comment83_0_, this_.country as country83_0_, this_.subscribe as subscribe83_0_, this_.imported as imported83_0_, this_.creation_datetime as creation10_83_0_ from mail_address this_ where TO_DATE(creationDateTime,'DD/MM/YYYY') between 'Wed Dec 01 11:03:47 CET 2010' and 'Thu Dec 02 11:03:47 CET 2010' order by this_.firstname asc, this_.lastname asc, this_.email asc]

Looks like the TO_DATE is passed along down to the database engine.

When running the same HQL statement against MySQL it also gave an error:

Quote:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: FUNCTION db_integration.TO_DATE does not exist

And apparently, the Expression type is deprecated too.

Are you testing against the same database engines as me ?

stephaneeybert · **Joined:** Mon Apr 16, 2007 8:10 am **Posts:** 246

The Expression type being deprecated, I'm advised to use the Restrictions type instead.

You tried that latter type ?

chirag.naikec · **Posted:** Wed Dec 01, 2010 6:24 am

Can you please give me details of datatype of creationDateTime,fromDateTime and toDateTime in your database??

I mean to say creationDateTime is date or timestamp or varchar?? Similarly for fromDateTime and toDateTime.

Chirag

stephaneeybert · **Joined:** Mon Apr 16, 2007 8:10 am **Posts:** 246

Sure !

Code:

   <typedef name="dateTime" class="org.jadira.usertype.dateandtime.joda.PersistentDateTime" />

Code:

        <property name="creationDateTime" type="dateTime">
            <column name="creation_datetime" not-null="true" />
        </property>

Code:

   private DateTime creationDateTime;

   public DateTime getCreationDateTime() {
      return this.creationDateTime;
   }

   public void setCreationDateTime(DateTime creationDatetime) {
      this.creationDateTime = creationDatetime;
   }

Code:

DateTime fromDateTime, DateTime toDateTime

public List<MailAddress> findByCreationDateTime(DateTime fromDateTime, DateTime toDateTime);

Code:

   public List<MailAddress> findByCreationDateTime(DateTime fromDateTime, DateTime toDateTime) {
      Criteria criteria = getSession().createCriteria(MailAddress.class);
      criteria.add(Restrictions.sqlRestriction("DATE(creationDateTime) >= ?)", fromDateTime.toDate(), org.hibernate.type.StandardBasicTypes.DATE));
      criteria.add(Restrictions.sqlRestriction("DATE(creationDateTime) <= ?)", toDateTime.toDate(), org.hibernate.type.StandardBasicTypes.DATE));
criteria.addOrder(Order.asc("firstname")).addOrder(Order.asc("lastname")).addOrder(Order.asc("email"));
      return criteria.list();
   }

chirag.naikec · **Posted:** Wed Dec 01, 2010 7:11 am

Quote:

Code:
private DateTime creationDateTime;

public DateTime getCreationDateTime() {
return this.creationDateTime;
}

public void setCreationDateTime(DateTime creationDatetime) {
this.creationDateTime = creationDatetime;
}

Code:
DateTime fromDateTime, DateTime toDateTime

public List<MailAddress> findByCreationDateTime(DateTime fromDateTime, DateTime toDateTime);

As your creationDateTime,fromDateTime and toDateTime is in DateTime format why you want to convert them to date? Why are you not directly using them in between clause??

Code:

criteria.add(Restrictions.between(creationDateTime, fromDateTime, toDateTime));

stephaneeybert · **Joined:** Mon Apr 16, 2007 8:10 am **Posts:** 246

Because, I need to have a comparison with the dates only, the times need to be ignored in that feature.