openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chris <cwolf.a...@gmail.com>
Subject Re: How to compare fields of type TemporalType.TIMESTAMP - but just by Date?
Date Thu, 12 Sep 2013 17:46:52 GMT
In my last post, I asked, "How can I do this in JPA?", but I meant to 
ask, "How can I do this in JPQL?".

I'm going to guess it's impossible because I'm not seeing any JPQL 
date/datetime manipulation functions in the docs, other then 
CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP, so I resorted to a 
native SQL query using the syntax shown in my initial post.

Thanks,


Chris

On 9/12/2013 1:12 PM, Chris wrote:
> I have an entity with a timestamp field, but for the purpose of a 
> certain query, I want to only compare by date - and ignore the time 
> component of the value.
>
> For example, in Oracle, the raw SQL would look like:
>
> SELECT t0.BASE_DATA_ID, t0.CLIENT_ID, t0.DB_USER, t0.OS_USER, 
> t0.UPSERT_DATE,
>        t0.CRNCY_CODE, t0.datadate, t0.ident, t0.LAST_PUBLISHED_DATE,
>        t0.PRICE_VAL_1, t0.PRICE_VAL_2, t0.TYPE_NAME
>        FROM MD_BASE_DATA t0
>        WHERE (t0.CLIENT_ID = 'JUNIT-1'
>        and to_date(to_char(t0.UPSERT_DATE,'YYYY-MM-DD'),'YYYY-MM-DD')
>        = to_date(to_char(current_date - 1,'YYYY-MM-DD'),'YYYY-MM-DD'))
>
> This query filters by field "UPSERT_DATE" being yesterday, ignoring 
> the time part.
>
> How can I do this in JPA?
>
> Thanks,
>
>
> Chris


Mime
View raw message