Hello Chris,
My entities all contain timestamp fields for the creation date of the record.
as a Test, I created the following:
<named-query name="Currency.created">
<query>
SELECT c
FROM Currency c
WHERE c.created <![CDATA[>=]]>
:created
ORDER BY c.code desc
</query>
</named-query>
@Test
public void test() {
EntityManagerFactory emf =
Persistence.createEntityManagerFactory("g11dev");
EntityManager em = emf.createEntityManager();
SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
try {
Timestamp date = new Timestamp(df.parse("20100101").getTime());
// This is a java.sql.Timestamp !!
TypedQuery<Currency> query =
em.createNamedQuery("Currency.created", Currency.class);
query.setParameter("created", date);
List<Currency> list = query.getResultList();
assertTrue(!list.isEmpty());
} catch (Exception pEx) {
pEx.printStackTrace();
fail();
}
}
The resulting query is:
SELECT t0.uniqueid, t0.versionid, t0.active, t0.created, t0.createdby,
t0.modified, t0.modifiedby, t0.code, t0.description,
t0.includeincustomerlisting, t0.includeinlisting
FROM galaxy11.currency t0
WHERE (t0.created >= ?)
ORDER BY t0.code DESC
[params=(Timestamp) 2010-01-01 00:00:00.0]
and works as expected.
Hope this helps.
Regards,
John
> -----Original Message-----
> From: Chris [mailto:[email protected]]
> Sent: Thursday, September 12, 2013 7:47 PM
> To: [email protected]
> Subject: Re: How to compare fields of type TemporalType.TIMESTAMP - but just
> by Date?
>
> 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
