Using HQL to query on a date while ignoring the time on Oracle

Question

I have a table (in Oracle 9 and up) where I need to find all entries for a given day using Hibernate. The entries have timestamps (with data type 'date'). Some of the entries have a time, others only have a date. This can not be changed, as this is the output of other applications which I can not change. In SQL I would write something along the lines of

SELECT * FROM table WHERE trim(table.date) = to_date('11.06.2009')

to get all entries for the date I am looking for. I was wondering how I can get Hibernate to do this using HQL. I know I can use SQL-queries in Hibernate, but this seems to be less clean. Is there a way to do this? If possible, this way should also work on non-oracle databases where timestamp would be the data type.

Solution 1

You could put two constraints into your HQL, one that specifies greater than or equal to the start of the date you are searching on, and one that specifies less than the next day.

i.e.

table.date >=11.06.2009 AND table.date < 11.07.2009

HQL (as well as SQL) also allows for:

table.date between 11.06.2009 AND 11.07.2009

Keep in mind between is always inclusive, meaning it's both >= and <= respectively.
More details on between here: http://www.coding-dude.com/wp/java/hibernate-java/hibernate-hql-between-expression/

Solution 2

I think there are two ways to solve this:

1. Use a criteria query and add a SQLRestriction. The restriction would take the form "trim({alias}.date) = ?". A problem here could be the conversion of the input parameter to the correct type on Oracle (or other DBMS). You can provide the necessary (hibernate) type as parameter, but if this is database dependent, it will be hardcoded.

2. use a formula in your hibernate mapping. The mapping would be:

    <class name="Person">
      <property name="birthDay" formula="trim(birthDay)"/>
    </class>

You can now use Hibernate's typing system to use a compare with a java object like this:

s.createCriteria(Person.class)
 .add(Restrictions.eq("birthDay", new java.sql.Date(System.currentTimeMillis())))
 .list()

One problem you'll have with both solutions is the trim function might not be available in the database. A solution for this (e.g. while testing on HSQLDB) is to put a import.sql on your classpath test (this will be picked up by Hibernate automatically) and create a procedure or function there. You can also Alter table Person add column birthDay timestamp in that file to make the generated schema work.

Solution 3

For CustomHQL, you can try the trunc keyword

For Ex:

SELECT T0.FirstName as FirstName ,T0.LastLoginDate as LastLoginDate  FROM User T0
WHERE (trunc(T0.LastLoginDate) >= :LastLoginDate) ORDER BY T0.LastLoginDate 

NOTE: Where :LastLoginDate is a Parameter.

This does the trick for me.

Comments

[will824]

Not good idea to work with constant values, as well as Setori said, there are months with less than 30 days and others with 31 days.