current_date and persistent date field

[Marc Logemann <ml...@logemann.org>]

Hi,

suppose i have a datetime field "created" in the DB which is mapped by  
JPA to a java.util.Date attribute in my domain class.

Now i want to create a query which is something like:

"select x from Order x where x.created = current_date"

The problem is. This never matches because current_date represents  
only the current date and in my Date attribute we have a complete Date  
with time. My intention is to compare just the date, but i have a  
java.util.Date attribute. It seems thats not possible at all.

Anyone with ideas? Thanks.

--
Marc Logemann
blog http://logemannreloaded.blogspot.com
privat http://www.logemann.org

RE: current_date and persistent date field

[Michael Vorburger <mv...@odyssey-group.com>]

Hello, Marc and Jeremy, others:

> I've been searching for a means to do something similar with JPQL but
haven't found anything yet

I'm wondering if the "SQL passthrough" extenion, see
http://n2.nabble.com/Queries-using-specific-SQL,-e.g.-functions,-or-spec
ific-operators---passthrough-SQL-pieces-without-full-native-query-td6688
56.html, could interest you?

> Can one do this with custom functions in OpenJPA ?

May be one could write your own JDBCFilterListener for a need like this?

It would be nice if this area was better documented...

Regards,
Michael
 

-----Original Message-----
From: Jeremy Bauer [mailto:techhusky@gmail.com] 
Sent: jeudi, 4. septembre 2008 21:14
To: users@openjpa.apache.org
Subject: Re: current_date and persistent date field

Marc,

It isn't very elegant, but you could try using a native query with a
cast to produce the result.  I was able to get the native query below to
produce only Orders for the current date using DB2. My 'created'
field is defined as a TIMESTAMP, which is similar to the DATETIME
datatype.

@NamedNativeQuery(name="todaysOrders",
        query="select * from ORDER where CAST(created AS DATE) =
current_date",
        resultClass=Order.class)

This is, of course, DB specific so it may or may not work for with your
DB (SQL Server?).  I've been searching for a means to do something
similar with JPQL but haven't found anything yet.

-Jeremy

____________________________________________________________

� This email and any files transmitted with it are CONFIDENTIAL and intended
  solely for the use of the individual or entity to which they are addressed.
� Any unauthorized copying, disclosure, or distribution of the material within
  this email is strictly forbidden.
� Any views or opinions presented within this e-mail are solely those of the
  author and do not necessarily represent those of Odyssey Financial
Technologies SA unless otherwise specifically stated.
� An electronic message is not binding on its sender. Any message referring to
  a binding engagement must be confirmed in writing and duly signed.
� If you have received this email in error, please notify the sender immediately
  and delete the original.

Re: current_date and persistent date field

[Jeremy Bauer <te...@gmail.com>]

Marc,

It isn't very elegant, but you could try using a native query with a
cast to produce the result.  I was able to get the native query below
to produce only Orders for the current date using DB2. My 'created'
field is defined as a TIMESTAMP, which is similar to the DATETIME
datatype.

@NamedNativeQuery(name="todaysOrders",
        query="select * from ORDER where CAST(created AS DATE) = current_date",
        resultClass=Order.class)

This is, of course, DB specific so it may or may not work for with
your DB (SQL Server?).  I've been searching for a means to do
something similar with JPQL but haven't found anything yet.

-Jeremy

Re: current_date and persistent date field

[Marc Logemann <ml...@logemann.org>]

Craig,

thanks for your answer. I got the message but somehow it feels that  
this is a JPA shortcoming.

I definitely want to store a complete DATETIME field in my DB because  
i most likely need the complete time for some things within my app.  
But for this one query, the only interessting thing is the date  
without time.

Of course the range thingy would somehow work but its ugly. What i am  
missing is something like this:

select x from Order x where date(x.created) = current_date

so a date() function on JPA level or something. Or how about general  
date related functions at all.
Similary there could be time() or timestamp() functions that modify  
the value of x.created before sending it to the DB.

Is this a weird idea? Can one do this with custom functions in OpenJPA ?

--
Marc Logemann
blog http://logemannreloaded.blogspot.com
privat http://www.logemann.org



Am 04.09.2008 um 01:32 schrieb Craig L Russell:

> Hi Marc,
>
> I think you have to decide if you need the time stored in the  
> database, or if that's just an artifact of the implementation.
>
> If you need the time, then you might have to do a range comparison  
> by calculating the date with time and adding a day, then comparing  
> x.created > some_date and x.created < some_date_plus_one_day.
>
> If you don't need the time, then you need to strip off the time part  
> of the date before you store it. Then an equality comparison with a  
> date will work. Calendar functions can do this for you. Simply doing  
> created = new Date() won't do it.
>
> Craig
>
> On Sep 3, 2008, at 4:05 PM, Marc Logemann wrote:
>
>> Hi,
>>
>> suppose i have a datetime field "created" in the DB which is mapped  
>> by JPA to a java.util.Date attribute in my domain class.
>>
>> Now i want to create a query which is something like:
>>
>> "select x from Order x where x.created = current_date"
>>
>> The problem is. This never matches because current_date represents  
>> only the current date and in my Date attribute we have a complete  
>> Date with time. My intention is to compare just the date, but i  
>> have a java.util.Date attribute. It seems thats not possible at all.
>>
>> Anyone with ideas? Thanks.
>>
>> --
>> Marc Logemann
>> blog http://logemannreloaded.blogspot.com
>> privat http://www.logemann.org
>>
>>
>>
>
> Craig L Russell
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> 408 276-5638 mailto:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
>

Re: current_date and persistent date field

[Craig L Russell <Cr...@Sun.COM>]

Hi Marc,

I think you have to decide if you need the time stored in the  
database, or if that's just an artifact of the implementation.

If you need the time, then you might have to do a range comparison by  
calculating the date with time and adding a day, then comparing  
x.created > some_date and x.created < some_date_plus_one_day.

If you don't need the time, then you need to strip off the time part  
of the date before you store it. Then an equality comparison with a  
date will work. Calendar functions can do this for you. Simply doing  
created = new Date() won't do it.

Craig

On Sep 3, 2008, at 4:05 PM, Marc Logemann wrote:

> Hi,
>
> suppose i have a datetime field "created" in the DB which is mapped  
> by JPA to a java.util.Date attribute in my domain class.
>
> Now i want to create a query which is something like:
>
> "select x from Order x where x.created = current_date"
>
> The problem is. This never matches because current_date represents  
> only the current date and in my Date attribute we have a complete  
> Date with time. My intention is to compare just the date, but i have  
> a java.util.Date attribute. It seems thats not possible at all.
>
> Anyone with ideas? Thanks.
>
> --
> Marc Logemann
> blog http://logemannreloaded.blogspot.com
> privat http://www.logemann.org
>
>
>

Craig L Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!