[FIXED] How to query the database based on current day using Hibernate?

Issue

I would like to retrieve database items based on the current day.

For that purpose I am trying to use the day() function as explained there and mentioned there.

I have a 'created' date field that is automatically filled when a new item is created in the table. It's configured using a model, like that:

@Column(name = "created", nullable = false)
@CreationTimestamp
private Date created;

I created a couple of items and the date is filled in the database as expected.

Now I am trying to call those items using a HQL query:

String hql = "" +
  "FROM Item as item " +
  "WHERE item.itemId = ?1" +
  "AND item.created = day(current_date())";

The problem is that this query is returning a NoResultException.

It's weird because I am using the current_date() function in other scenarios, e.g.:

String hql = "" +
  "FROM Item as item " +
  "WHERE item.itemId = ?1" +
  "AND item.createdAt >= current_date - ?2";

And it is working pretty well!

So I assume the issue is related with day() function.

Solution

Try use this:

String hql = "" +
  "FROM Item as item " +
  "WHERE item.itemId = ?1" +
  "AND date(item.created) = current_date()";

Answered By - Aliaksandr Veramkovich