CODEWITHSUNDEEP
oracle-databaseselectjpa
Gergely Kovács
Gergely Kovács

Reputation: 599

Oracle + JPA - querying with INTERVAL

I have a Timestamp column in my DB table, LASTUPDATED, which I poll to decide if a row should be updated or not. If the record was not updated in the last 10 minutes, I update it. I want to delegate the date handling to the DB, but none of the following works:

This one says "enexpected token: : near[...]"

    Query query = entityManager.createQuery("SELECT x FROM MyEntity x WHERE x.lastUpdated < SYSTIMESTAMP - INTERVAL :olderThen MINUTE");
    query.setParameter("olderThen", 10);
    list = query.getResultList();

This one says "unexpected token: '10' near[...]

    Query query = entityManager.createQuery("SELECT x FROM MyEntity x WHERE x.lastUpdated < SYSTIMESTAMP - INTERVAL '10' MINUTE");
    list = query.getResultList();

And this one says unexpected token: '?' near[...]

    Query query = entityManager.createQuery("SELECT x FROM MyEntity x WHERE x.lastUpdated < SYSTIMESTAMP - INTERVAL ?1 MINUTE");
    query.setParameter(1, 10);
    list = query.getResultList();

Can I use this INTERVAL keyword with JPA by the way? The expression is correct syntactically, I tested it with console.

Thanks for the help Guys, Gergely

Upvotes: 9

Views: 13545

Answers (2)

koSiukaS
koSiukaS

Reputation: 11

This worked for me:

'10 minutes'::interval

Upvotes: 0

Gergely Kov&#225;cs
Gergely Kov&#225;cs

Reputation: 599

JPA and Hibernate does not sopport interval, finally I found it...

Date arithmetic is also supported, albeit in a more limited fashion. This is due partially to differences in database support and partially to the lack of support for INTERVAL definition in the query language itself. Source: http://docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html/ch11.html

Only the BETWEEN keyword can be used. Take care

Btw the correct expression was: 

     select x from MyEntity x where cast((systimestamp - (1/24/60) * 10) as timestamp) between lastUpdated and systimestamp

Upvotes: 8

Related Questions