1

I have a postgres database that has a table "draft" containing a column set_up_time, saved as a timestamp:

@Column(nullable = false)
@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentLocalDateTime")
LocalDateTime setUpTime;

For certain reasons I need to keep this structure as it is. However I am expecting database queries searching for records, for setUpTime as LocalDateTime and setUpTime as LocalDate. How can I map it in a way, that hibernate queries the database with both these types for setUpTime, and returns the results accordingly?

The schema I am using is :

"create table draft (dtype varchar(31) not null, id int8 not null, creation_time timestamp not null, cust_ord varchar(1), description varchar(255), amount decimal(12, 2), user varchar(6), txn_time timestamp, text1from varchar(36), text2from varchar(36), text3from varchar(36), primary key (id))"
neoInfinite
  • 341
  • 2
  • 4
  • 12
  • I assume you do store time information; or do you merely store the date part (meaning you set time values explicitly to 0)? Can you specify the expected behaviour in case someone searches with a `LocalDate`? – skirsch May 21 '13 at 13:53
  • Yes, I do store time information always. However, if someone searches with a date only, I should return them all the records matching the given date in their datetime field, no matter what the time stamp on them is. – neoInfinite May 28 '13 at 08:04

1 Answers1

0

What you can do is write your queries like this:

public void findDrafts(LocalDate targetDate) {
    final Query query = entityManager.createQuery(
      "from Draft where setUpTime => :beginOfDay and setUpTime < :beginOfNextDay");
    query.setParameter("beginOfDay", targetDate.toDateTimeAtStartOfDay().toLocalDateTime());
    query.setParameter("beginOfNextDay", targetDate.plusDays(1).toDateTimeAtStartOfDay().toLocalDateTime());
    return query.getResultList();
}
skirsch
  • 1,640
  • 12
  • 24