根据db的日期获取结果，而不考虑时间

I want to fetch the results which occurred on a particular date from the database. The problem is the database contains date+time but I require result on the basis of date part only.

我想从数据库中获取特定日期发生的结果。问题是数据库包含日期+时间,但我只需要基于日期部分的结果。

@Query("from TableA where traveller = ?1 and direction = ?2 and targetDate = ?3")
List<TableA> findListOfRequestsWithDateAndTraveller(String traveller, String direction, Date date);

2 个解决方案

#1

Try with the date function, found here: date(timestamp_field)

尝试使用日期函数,在这里找到:date(timestamp_field)

@Query("from TableA where traveller = ?1 and direction = ?2 and date(targetDate) = ?3")
List<TableA> findListOfRequestsWithDateAndTraveller(String traveller, String direction, Date date);

#2

I resolved this problem by handling it under java instead of query.

我通过在java而不是查询下处理它来解决这个问题。

@Query("from TableA where traveller = ?1 and direction = ?2 and targetTime >= ?3 and targetTime <= ?4")
List<PBillableRequest> findListOfRequestsWithDateAndTraveller(String traveller, String direction, Date startDate, Date endDate);

for getting the startDate and endDate, i used this functions.

为了获取startDate和endDate,我使用了这个函数。

public Date getEndOfDay(Date date) {
    Calendar calendar = Calendar.getInstance();
    calendar.setTime(date);
    calendar.set(Calendar.HOUR_OF_DAY, 23);
    calendar.set(Calendar.MINUTE, 59);
    calendar.set(Calendar.SECOND, 59);
    calendar.set(Calendar.MILLISECOND, 999);
    return calendar.getTime();
}

public Date getStartOfDay(Date date) {
    Calendar calendar = Calendar.getInstance();
    calendar.setTime(date);
    calendar.set(Calendar.HOUR_OF_DAY, 0);
    calendar.set(Calendar.MINUTE, 0);
    calendar.set(Calendar.SECOND, 0);
    calendar.set(Calendar.MILLISECOND, 0);
    return calendar.getTime();
}

#1