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

Reply via email to