CODEWITHSUNDEEP
javaoracle-databasejpatimestampcriteriaquery
Javatar
Javatar

Reputation: 703

JPA CriteriaQuery compare Timestamp ignore time portion

Let say I have a column in Oracle DB like this:

SOMETHING_TS TIMESTAMP WITH TIME ZONE

And I would like to use CriteriaQuery to filter by this column.

I can used native query to achieve this:

    SELECT * 
    FROM SOMETHING
    WHERE TRUNC(SOMETHING_TS) = TO_DATE('2016-12-08','YYYY-MM-DD');

But in Java I have failed to do so, below is my sample code:

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<MyClass> cq = cb.createQuery(MyClass.class);
    Date date = new Date();

    predicates.add(cb.equal(cb.function("TRUNC", Date.class, myClass.get("somethingTs")), cb.function("TO_DATE", Date.class, cb.parameter(Date.class, "somethingTs"), cb.literal("YYYY-MM-DD"))));

    Predicate[] predArray = new Predicate[predicates.size()];
    predicates.toArray(predArray);

    cq.where(predArray);

    TypedQuery<MyClass> query = em.createQuery(cq);
    query.setParameter("somethingDt", date);

Upvotes: 5

Views: 25272

Answers (4)

Ladgelson
Ladgelson

Reputation: 11

A simple solution that worked for me. I create a Calendar variable setting the day I want to compare. But I truncated the date to consider until days and discosidering hours, minutes and seconds.

So the idea is trunc using Calendar to prevent using CriteriaBuilder.function().

List<Predicate> predicates = new ArrayList<>();
Join<A, B> joinB = a.join("B");
Calendar startOfTheCurrentDay = GregorianCalendar.from(
                    ZonedDateTime.ofInstant(Instant.now().truncatedTo(ChronoUnit.DAYS), ZoneId.systemDefault()));

predicates.add(
    criteriaBuilder.lessThanOrEqualTo(
        criteriaBuilder.literal(startOfTheCurrentDay),
        joinB.<Calendar>get("expirationDate")
    )
);

Upvotes: 0

abhihello123
abhihello123

Reputation: 1728

Javatar's answer even if works is not perfect since to_date returns Date and not String. Also there is not much point to use a parameter, better use a literal. Here is my answer which would work for anyone.

predicates.add(criteriaBuilder.lessThanOrEqualTo(criteriaBuilder.function("TRUNC", java.sql.Date.class, root.get("executionDateTime")),
                                                                 criteriaBuilder.function("TO_DATE", java.sql.Date.class, criteriaBuilder.literal(request.getExecutionToDate().toString()),
                                                                         criteriaBuilder.literal("yyyy-mm-dd"))));

Upvotes: 0

MitchBroadhead
MitchBroadhead

Reputation: 899

Javatar's answer didn't solve my specific case but here is what helped me in case it helps anyone.

private List<MyClass> selectMyClassByDate(Date date) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<MyClass> cq = cb.createQuery(MyClass.class);
    Root<MyClass> myClass = cq.from(MyClass.class);
    cq.select(myClass);
    cq.where(cb.equal(cb.function("date", Date.class,
        myClass.get(MyClass_.timestamp)), date));
    final TypedQuery<MyClass> tq = em.createQuery(cq);
    log.fine(
        tq.unwrap(org.apache.openjpa.persistence.QueryImpl.class)
            .getQueryString());
    List<MyClass> myClassList = tq.getResultList();
    return myClassList;
}

sorry this only applies to MYSQL

Upvotes: 2

Javatar
Javatar

Reputation: 703

Seems like I have found my answer:

predicates.add(cb.equal(cb.function("TRUNC", Date.class, myClass.get("somethingTs")), cb.function("TO_DATE", String.class, cb.parameter(String.class, "somethingTs"), cb.literal("YYYY-MM-DD"))));

Then when I set parameter, I will set the String format of date to query.

Thanks!

Upvotes: 15

Related Questions