JPA query date setParameter

[javabuddy]

I have an entity which has Date field as one of the column. I have a
query to extract the entites which match the date that I send in as
parameter,

Query:
select o from Assignment o where o.assignmentDueDate = :date

(Setting parameter)
assignmentsDueForDay.setParameter("date", new Date(),
TemporalType.DATE);

BUT this doesnt yeild me any results, I confirmed the table has lot of
entities for the date I am trying to extract.

Please help.

-Gana

[zixzigma]

I believe in the line below:
assignmentsDueForDay.setParameter("date", new Date(),TemporalType.DATE);

you are passing "new Date()", which is the current time/date on the system, and is not the date you are looking for.
you need to pass the Date you are looking for, not the current time.

i've read in previous posts, that its better to create a DataTransferObject for your Date (day/month/year).
and pass it to the server, because the time/date on the client would likely be different than server (considering time zones)/

make sure you use an appropriate date, instead of new Date().

this is all I am guessing, I might be wrong.

[javabuddy]

In the above scenario, I am trying to get the assignments that are due
on the current day, thats the reason I am sending new Date() as
parameter value.

[al...@mechnicality.com]

On 1/23/2011 5:29 AM, javabuddy wrote:
> In the above scenario, I am trying to get the assignments that are due
> on the current day, thats the reason I am sending new Date() as
> parameter value.

A java Data object is a wrapper for a time in milliseconds. If a and b are Date objects, then
a.equals(b) only when the millisecond time of a is exactly the same as the millisecond time of b.
If you are searching for the same day value you'll have to do something a bit cleverer with your query.

If you are using JPQL it seems to be seriously lacking the functions to select elements of the date.
The best suggestion that I've seen is that in your DAO function you pass in two values, one for the
first millisecond of the day and one for the last, and compare your actual date value to see if it
lies between them.

The alternative, which depends upon your system configuration, is to use an SQL native query -
depending upon the db you are using there are normally 'day' 'hour' 'minute' etc functions. For
example in postgres, you can use EXTRACT(DAY FROM TIMESTAMP, 'date') - but that won't work in JPQL.

See here for a further discussion - at the end Quintin Beukes discusses it pretty well:

> http://old.nabble.com/how-to-compare-2-dates-in-a-JPA-QL-query-td18959789.html

Regards

Alan

[Jeff Schwartz]

You need to query on a 24 hour time range

> --
> You received this message because you are subscribed to the Google Groups "Google Web Toolkit" group.
> To post to this group, send email to google-we...@googlegroups.com.
> To unsubscribe from this group, send email to google-web-tool...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/google-web-toolkit?hl=en.
>

[al...@mechnicality.com]

On 1/23/2011 7:33 AM, Jeff Schwartz wrote:

You need to query on a 24 hour time range

Yes, and, of course, you have to decide on what 'day' you are looking for - the users' day, the server's day, UTC, or some arbitrary spot on Earth. And then there's day light saving... its surprising how difficult it can get to search for a date!

Alan

-- 
Alan Chaney
CTO and Founder, Mechnicality, Inc.
www.mechnicality.com