Date query with Hibernate on Timestamp Column in PostgreSQL

Posted by Shashikant Kore on Stack Overflow See other posts from Stack Overflow or by Shashikant Kore
Published on 2010-03-30T07:35:39Z Indexed on 2010/03/30 7:43 UTC
Read the original article Hit count: 596

Filed under:
|
|
|

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.

© Stack Overflow or respective owner

Related posts about hibernate

Related posts about postgresql