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.

I believe between is always exclusive on both limits. Why don't you just remove 1 millisecond and add 1 millisecond to each of them? — jishi
– jishi, Commented Mar 30, 2010 at 8:03
@jishi it's database dependent (and most of time inclusive I believe). — Pascal Thivent
– Pascal Thivent, Commented Mar 30, 2010 at 8:09

Pascal Thivent · Accepted Answer · 2010-03-30 09:59:09Z

3

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

edited Mar 30, 2010 at 9:59

answered Mar 30, 2010 at 8:05

Pascal Thivent

572k140 gold badges1.1k silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Shashikant Kore Over a year ago

type="date" doesn't help. Want to try your earlier idea of annotation, but unfortunately, I'm on Hibernate version older than 3.3 in which annotations were introduced.

Pascal Thivent Over a year ago

@Shashikant I posted my answer a bit too fast, didn't realize you were using xml mappings (hence the update). But indeed, Hibernate annotations has a @Temporal annotation for this (docs.jboss.org/hibernate/stable/annotations/reference/en/…). There must be some kind of equivalent with xml... Do you have control on your physical model BTW?

Shashikant Kore Over a year ago

Except DB schema (ie timstamp column) and Hibernate version, I can change everthing else. Temporal annotation sounds useful but as I mentioned, I can't move to Hibernate 3.3+. Also, if I add Temporal annoation as Date, while retrieving the row, will it truncate timestamp to date? If yes, that is undesirable. Thanks for the help, Pascal. Modding +1.

KJEjava48 Over a year ago

@PascalThivent using Temporal annotation as date skips the gson serialization on api request and hence getting previous date on client side.That if dob on database is '13-11-1987' then getting it as '12-11-1987' as rest api response.

KJEjava48 Over a year ago

@PascalThivent See stackoverflow.com/questions/58688079/…

Tomislav Nakic-Alfirevic · Accepted Answer · 2010-03-30 08:02:57Z

1

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.

answered Mar 30, 2010 at 8:02

Tomislav Nakic-Alfirevic

10.2k5 gold badges42 silver badges51 bronze badges

3 Comments

Michael Buen Over a year ago

still that wouldn't fly, that will include 2010-04-01 00:00 AM

Tomislav Nakic-Alfirevic Over a year ago

Using lt("endDate", endDate) where endDate = "2010-04-01 00:00" would include records with end date 2010-04-01 00:00? I don't think that is the case.

Shashikant Kore Over a year ago

Thanks for link for 'between' being inclusive for PostgreSQL. Boundary conditions is not much of a worry as timestamps are recorded only to the precision of seconds. To use, less-than or greater-than, I need to use less-than("2010-04-01 00:00:00") and greater-than-or-equal-to("2010-03-30 00:00:00")

Frank Heikens · Accepted Answer · 2010-03-30 08:00:12Z

0

Maybe you can CAST orderTimestamp to a DATE. HQL does have support for this.

answered Mar 30, 2010 at 8:00

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Comments

Donato Szilagyi · Accepted Answer · 2013-02-05 19:24:47Z

0

Use cast(your_data, date), remember the second arg of the cast function must be lower, or it wont work.

Att.: Samuel\Maycon

edited Feb 5, 2013 at 19:24

Donato Szilagyi

4,3654 gold badges40 silver badges54 bronze badges

answered Feb 5, 2013 at 18:59

user2044365

1

Collectives™ on Stack Overflow

Date query with Hibernate on Timestamp Column in PostgreSQL

4 Answers 4

5 Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related