2

I have entity Event with following fields:

@Temporal(TemporalType.TIMESTAMP)
@JsonFormat(pattern = DATE_FORMAT)
private Date start;

@Column(name = "period_minutes")
private Integer period;

I need to compare an amount of these fields with current date time:

SELECT * FROM Event e WHERE (e.start + e.period) < NOW();

Other words, I need add to start field (which is date) some amount of minutes from period (for example 15) and compare it with current datetime. I tried to do this like:

return (root, query, cb) -> {
    Expression<Long> sum = cb.sum(root.<Date>get("start"), root.get("period"));
    return cb.lessThan(sum, new Date());
};

But this does not compile. And I don't know how to convert the date to long and add period * 60 * 1000 (minutes -> ms). How to fix this issue?

SOLUTION

I have fixed this issue with help of @Серг0

cb.lessThan(
    cb.sum(
        cb.function("unix_timestamp", Long.class, root.get("start")),
        cb.prod(root.get("period"), 1L)
        ),
    cb.function("unix_timestamp", Long.class, cb.function("now", Long.class))
);

But I can't understand why if I use 1L it works, and if I use 60L*1000L (because I need to calculate milliseconds of period that is in minutes) it does not work.

Oleksandr H
  • 2,965
  • 10
  • 40
  • 58
  • see http://www.objectdb.com/database/forum/442 last post, I think that's what you want –  Sep 04 '17 at 16:48

1 Answers1

2

Try this:

Specification<Event> predicate = (root, query, cb) -> {

    Expression<Time> currentTime = cb.currentTime();

    return cb.and(
        cb.equal(root.get("start"), cb.currentDate()), // if start date == current date
        cb.lessThan( // if period < current hour * 60 + current minutes
            root.get("period"), 
            cb.sum( // current hour * 60 + current minutes
                cb.prod(cb.function("hour", Integer.class, currentTime), 60), // hours * 60
                cb.function("minute", Integer.class, currentTime) // current minutes
            )
        )
    );
};

List<Event> events = findAll(predicate);

Additional info:

Criteria Query Arithmetic Expressions

Date and Time in Criteria Queries

Cepr0
  • 28,144
  • 8
  • 75
  • 101
  • do you mean `start` and period as `root.get("start")`, `root.get("period")`? I don't understand this approach – Oleksandr H Sep 04 '17 at 20:55
  • @CatH замени тип java.util.Date на java.sql.Date для поля `start` и замени строку return на мою, и проверь, заработает ли... – Cepr0 Sep 04 '17 at 21:09
  • @CatH Пардон, ответ некорректный, увлекся... )) – Cepr0 Sep 05 '17 at 06:04