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.

Solution 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.

Solution 2

The "2010-03-31 23:59:59." border condition is potentially dangerous: if there was a transaction in the system between 23:59:59 and 00:00:00, you'd miss it. If you have enough transactions daily and a long enough period, a report is going to break and you'll spend hours to find out why.

PostgreSQL between includes the limits so it would not even be safe to set the end date to 2010-04-01 00:00:00. Use lt() and gt() to be sure.