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 Mon, 16 Sep 2013 15:09:38 GMT
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.algo@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


Mime
View raw message