Fetch results on the basis of date from db without considering time

Question

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);

Which DB are you using, for Oracle, you can trunk the time and filter on dates — Saurabh Jhunjhunwala, May 04 '15 at 11:05
@SaurabhJhunjhunwala which is the best way to kill indices on date-time columns. What should be done is a `between` with the particular day at midnight (include) as the lower bound and the next day at midnight (exclude) as the upper bound. — fge, May 04 '15 at 11:09
@fge: `BETWEEN` would be subtly inaccurate, because it *includes* lower and upper bound. To be precise: `x >= date AND x < (date + 1)`. — Erwin Brandstetter, May 04 '15 at 12:06

score 0 · Answer 1 · edited May 23 '17 at 12:29

0

Try with the date function, found here: 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);

edited May 23 '17 at 12:29

Community

1
1

answered May 04 '15 at 11:46

Ignotus

301
1
10

score 0 · Accepted Answer · answered May 05 '15 at 14:42

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

@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.

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();
}

Fetch results on the basis of date from db without considering time

2 Answers2