Extracting date from timestamp in HQL for comparison in a where clause

Question

I want to extract (year, month, dayOfMonth) from a timestamp field using HQL for comparison in a where clause.

Example:

Query hibernateQuery = session.createQuery("select sum(t.amount) from Transaction t " + 
  "where extract_date(t.timestampField) = :date");

hibernateQuery.setParameter("date", LocalDate.of(2018, 10, 11));

System.out.println( hibernateQuery.getSingleResult() );

So I am looking for a function like this pseudo function extract_date(t.timestampField) that I've used in example above.

What is the type of timestampField column? – Vivek Jan 06 '20 at 07:45 — Vivek, Jan 06 '20 at 07:45
@Vivek it is `java.time.LocalDateTime` – mnrafg Jan 06 '20 at 07:56 — mnrafg, Jan 06 '20 at 07:56

score 2 · Accepted Answer · edited Jan 06 '20 at 08:03

This should work properly

Query hibernateQuery = session.createQuery("select sum(t.amount) from Transaction t " + 
      "where DATE(t.timestampField) = :date");

hibernateQuery.setParameter("date", new java.sql.Date.valueOf("2018-10-11"));

Note: the data type returned by DATE(...) function is java.sql.Date so if you are having a java.time.LocalDate it can be easily converted to java.sql.Date as follow:

LocalDate localDate = LocalDate.of(2018, 10, 11);
java.sql.Date sqlDate = java.sql.Date.valueOf(localDate);

It is discussed in the following Q/A.

https://stackoverflow.com/a/29168526/7972796

Extracting date from timestamp in HQL for comparison in a where clause

1 Answers1