Jadira is the home for Sousan and Chris Pheby's open source projects. These are reusable open source Java modules that provide first class solutions using the most effective current JEE technologies.

Tag Cloud

Jadira Support > PersistentDateTimeWithZone with '>=' where clause

Duplicated from StackOverflow (http://stackoverflow.com/questions/16646475/hql-where-clause-greater-than-with-jadira-persistentdatetimewithzone) incase the maintainers of Jadira are not watching there....

I'm using jadira PersistentDateTimeWithZone to store a joda DateTime with timezone. It all works as expected, except for searching for dates using ">=" - it compares the time timezone string in the where clause (see SQL at bottom). Is there any way to further refine my annotaion or HQL to prevent this, or is it a bug in Jadira

@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTimeWithZone")
@Columns(columns={@Column(name="departureDate", nullable = false),@Column(name="departureDateTimezone", nullable = false)})
private DateTime depScheduled;

My HQL query in my JpaRepository:

* Get all things that depart after the given date
@Query("select e from MyTable e where depScheduled>=?1")
List<MyTable> loadDatFromMyTable(DateTime depDate);

The generated SQL - see it is trying to compare both the date and the timezone string.

x.departureDate as departur5_4_,
x.departureDateTimezone as departur6_4_,
MyTable x
and crewroster0_.departureDate>=?
and crewroster0_.departureDateTimezone>=?

May 20, 2013 | Unregistered CommenterKevin

The type is actually a Hibernate CompositeType (like all of the Jadira multicolumn types). This lets you query on the components of the type. For this type the two components are: 'datetime' and 'offset', therefore, try referring to the component within the type as depScheduled.datetime.

There is a good discussion of CompositeTypes at Learning Via Code

I've answered this also on StackOverflow.

June 29, 2013 | Registered CommenterChris