Date query with Hibernate on Timestamp Column in PostgreSQL

Question

A table has timestamp column. A sample value in that could be 2010-03-30 13:42:42. With Hibernate, I am doing a range query Restrictions.between("column-name", fromDate, toDate).

The Hibernate mapping for this column is as follows.

<property name="orderTimestamp"  column="order_timestamp" type="java.util.Date" />

Let's say, I want to find out all the records that have the date 30th March 2010 and 31st March 2010. A range query on this field is done as follows.

Date fromDate = new SimpleDateFormat("yyyy-MM-dd").parse("2010-03-30");
Date toDate = new SimpleDateFormat("yyyy-MM-dd").parse("2008-03-31");
Expression.between("orderTimestamp", fromDate, toDate);

This doesn't work.

The query is converted to respective timestamps as "2010-03-30 00:00:00" and "2010-03-31 00:00:00". So, all the records for the 31st March 2010 are ignored.

A simple solution to this problem could be to have the end date as "2010-03-31 23:59:59." But, I would like to know if there is way to match only the date part of the timestamp column.

Also, is Expression.between() inclusive of both limits? Documentation doesn't throw any light on this.

Answer 1

, I would like to know if there is way to match only the date part of the timestamp column.

If you don't want a TIMESTAMP, use a DATE (and shouldn't you use type="date"?).

Also, is Expression.between() inclusive of both limits?

It seems that the behavior of the SQL BETWEEN operator is database dependent. With PosgresQL, the BETWEEN expression:

x BETWEEN y AND z

is semantically equivalent to:

y <= x AND x <= z

i.e. inclusive.