In my Data base dates are as 2012-04-09 04:02:53 2012-04-09 04:04:51 2012-04-08 04:04:51, etc, I need to retrieve data which have current date in there date field. I mean i need to match only 2012-04-09' . How can i do it using hibernate criteria.
Add a comment
|
8 Answers
Use Restrictions.between() to generate a where clause which the date column is between '2012-04-09 00:00:00' and '2012-04-09 23:59:59'
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date fromDate = df.parse("2012-04-09 00:00:00");
Date toDate = df.parse("2012-04-09 23:59:59");
criteria.add(Restrictions.between("dateField", fromDate, toDate));
Please note that all the properties used in the Criteria API is the Java property name , but not the actual column name.
Update: Get fromDate and toDate for the current date using JDK only
Calendar calendar = Calendar.getInstance();
calendar.set(Calendar.HOUR_OF_DAY, 0);
calendar.set(Calendar.MINUTE, 0);
calendar.set(Calendar.SECOND, 0);
Date fromDate = calendar.getTime();
calendar.set(Calendar.HOUR_OF_DAY, 23);
calendar.set(Calendar.MINUTE, 59);
calendar.set(Calendar.SECOND, 59);
Date toDate = calendar.getTime();
criteria.add(Restrictions.between("dateField", fromDate, toDate));
4 Comments
Romi
How to get fromDate and toDate from Currentdate
OO7
@Ken Chan Thank you so much buddy for posting date comparison of single date. I'll give you +1 for
23:59:59 in parse method.
Olakunle Awotunbo
This was helpful. I think hibernate should provide a function to compare date without time.
EFreak
While this works for all practical purposes. 23:59:59 is still not exactly EOD. I would let java figure this out for me: LocalDate.now().atTime(LocalTime.MAX).atZone(ZoneId.systemDefault()).toInstant()
Like this?
criteria.add(Expression.eq("yourDate", aDate))
2 Comments
titogeo
How about using Expression.between("yourDate", aDate, aDate+1)
Phil_Charly
This didn't work for me , I think it does the exact much , the best case to compare dates is the first answer.
fromDate.setTime(new Date());
fromDate.set(Calendar.HOUR_OF_DAY, 0);
fromDate.set(Calendar.MINUTE, 0);
fromDate.set(Calendar.SECOND, 0);
fromDate.set(Calendar.MILLISECOND, 0);
This code is extremely dangerous ... if the day is switch to daylight saving time (e.g. 06.04.1980) you end up in following exception!!!
java.lang.IllegalArgumentException: HOUR_OF_DAY: 0 -> 1day
HQL: from human pat where year(pat.birthdate) = :start_day and month(pat.birthdate) = :start_month and year(pat.birthdate) = :start_year ");
params.put("start_day", startDate.get(Calendar.DAY_OF_MONTH));
params.put("start_month", startDate.get(Calendar.MONTH) + 1);
params.put("start_year", startDate.get(Calendar.YEAR));
The year/month/day functions use the underlying db functions (extract, ...) and compares only these values. Therefore I did not need to set the time to 0 which leads to the above described exception. just an example out of my mind how I solved the problem! Maybe it helps
Comments
How-to do it in Hibernate has already been said. You can prepare the Timestamp objects in the Java code using, for example, the following aproach:
Calendar cFrom = Calendar.getInstance();
cFrom.setTime(new Date()); /* today */
cFrom.set(Calendar.HOUR_OF_DAY, 0);
cFrom.set(Calendar.MINUTE, 0);
cFrom.set(Calendar.SECOND, 0);
cFrom.set(Calendar.MILLISECOND, 0);
Timestamp from = new Timestamp(cFrom.getTime().getTime());
Calendar cTo = Calendar.getInstance();
cTo.setTime(new Date()); /* today */
cTo.set(Calendar.HOUR_OF_DAY, 23);
cTo.set(Calendar.MINUTE, 59);
cTo.set(Calendar.SECOND, 59);
cTo.set(Calendar.MILLISECOND, 999);
Timestamp to = new Timestamp(cTo.getTime().getTime());
final String QUERY = ""
+ "SELECT tr "
+ "FROM Type tr "
+ "WHERE tr.timestamp >= :timestampFrom AND tr.timestamp <= :timestampTo";
Query query = entityManager.createQuery(QUERY);
query.setParameter("timestampFrom", from);
query.setParameter("timestampTo", to);
@SuppressWarnings("unchecked")
List<Type> ts = (List<Type>)query.getResultList();
Comments
The easiest way is to fetch all records having date between the beginning and end of a given day:
WHERE date BETWEEN :from AND :to
And compute from and to in your Java code.
For computing midnights:
import static org.apache.commons.lang.time.DateUtils.ceiling;
import static org.apache.commons.lang.time.DateUtils.truncate;
Date someDay = new Date();
Date from = truncate(someDay, Calendar.DAY_OF_MONTH);
Date to = new Date(ceiling(someDay, Calendar.DAY_OF_MONTH).getTime() - 1);
answered Apr 9, 2012 at 13:36
Tomasz Nurkiewicz
342k72 gold badges713 silver badges680 bronze badges
3 Comments
Romi
i dint get it. ceiling and truncate is for ?
Tomasz Nurkiewicz
@Romi: you need to somehow compute the beginning of a day (
Mon Apr 09 00:00:00 CEST 2012) and the end of it (Mon Apr 09 23:59:59 CEST 2012).Tomasz Nurkiewicz
@Romi: yes, truncate returns the beginning of a given day (00:00) and ceiling - the beginning of the next day.
Restrictions.between("dateColumn", midnight1, midnight2)
Comments
The following code will work
Calendar fromDate = Calendar.getInstance();
fromDate.setTime(new Date());
fromDate.set(Calendar.HOUR_OF_DAY, 0);
fromDate.set(Calendar.MINUTE, 0);
fromDate.set(Calendar.SECOND, 0);
fromDate.set(Calendar.MILLISECOND, 0);
Calendar toDate = Calendar.getInstance();
toDate.setTime(new Date());
toDate.set(Calendar.HOUR_OF_DAY, 23);
toDate.set(Calendar.MINUTE, 59);
toDate.set(Calendar.SECOND, 59);
toDate.set(Calendar.MILLISECOND, 999);
criteria.add(Restrictions.between("loadDate", fromDate.getTime(),
toDate.getTime()));
