John,
I see where you're going with that - but I need a query that selects
data on a single given date, not "greater or equal" to the date. So I
took your idea, but used BETWEEN and an extra date, like so:
select bd from MdBaseData bd
where bd.upsertDate between :upsertDateStart and :upsertDateEnd
SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
Date upsertDateStart = df.parse("20130913");
Date upsertDateEnd = df.parse("20130914");
The resulting generated SQL is as follows:
SELECT t0.BASE_DATA_ID, t0.CRNCY_CODE, t0.datadate, t0.ident,
t0.LAST_PUBLISHED_DATE, t0.PRICE_VAL_1, t0.PRICE_VAL_2,
t0.TYPE_NAME,
t0.UPSERT_DATE FROM MD_BASE_DATA t0
WHERE (t0.UPSERT_DATE >= ? AND t0.UPSERT_DATE <= ?)
[params=(Timestamp) 2013-09-13 00:00:00.0, (Timestamp)
2013-09-14 00:00:00.0]
Now this query will return data on a single date, September 13th, well,
also with a one second overlap
into Sept 14th, but operationally there will never be data inserted at
that second of time.
Thanks for you help!
Regards,
Chris
On 9/13/2013 3:05 AM, Boblitz John wrote:
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:cwolf.a...@gmail.com]
Sent: Thursday, September 12, 2013 7:47 PM
To: users@openjpa.apache.org
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
