Date request with Hibernate in a Timestamp column in PostgreSQL

Question

The table has a timestamp. The approximate value in this may be 2010-03-30 13:42:42. With Hibernate, I am performing a limited range query. Between them ("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 entries that have a date of March 30, 2010 and March 31, 2010. The range request in this field is performed 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 does not work.

The request is converted to the corresponding timestamps as "2010-03-30 00:00:00" and "2010-03-31 00:00:00". Thus, all entries for March 31, 2010 are ignored.

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

Also, does Expression.between() include both restrictions? The documentation does not shed light on this.

Answer 1

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

If you do not want TIMESTAMP, use DATE (and you should not use type="date" ?).

Also, is there an Expression.between () expression that includes both restrictions?

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

 x BETWEEN y AND z 

semantically equivalent:

 y <= x AND x <= z 

i.e. inclusive.

Answer 2

The "2010-03-31 23:59:59." The boundary condition is potentially dangerous: if the system had a transaction between 23:59:59 and 00:00:00, you would have missed it. If you have enough daily transactions and a sufficiently long period, the report will be broken and you will spend hours to find out why.

PostgreSQL between contains restrictions , so it would even be safe to set the end date to 2010-04-01 00: 00:00. Use lt () and gt ().

Answer 3

Perhaps you can CAST orderTimestamp with a DATE. HQL has support for this.

Answer 4

Use cast (your_data, date), remember that the second argument to the cast function must be lower or it does not work.

Att: Samuel \ Micon