CODEWITHSUNDEEP
javahibernate
pri_dev
pri_dev

Reputation: 11645

getting all date values in a date range in hibernate

I need to know how do I get all dates in a date range in Hibernate query resultset.

It should be something similar to the following pseudo code in SQL

Select "ALL DATES" from ???? where date between TO_DATE('date-val','format') and TO_DATE('date-val','format').

It may require some different logic but if I give a range like 5-Feb-2011 to 2-March-2011 it should return me all dates in that range in the resultset... i.e

resultset = 5-Feb-2011, 6-Feb-2011,..... 28-Feb-2011, 1-March-2011, 2-March-2011


Update: the following query is in Oracle gives the required result set

select 
    d.dateInRange as dateval,
    eventdesc,
    nvl(td.dist_ucnt, 0) as dist_ucnt
from (
    select 
        to_date('03-NOV-2011','dd-mon-yyyy') + rownum - 1 as dateInRange
    from all_objects
    where rownum <= to_date('31-DEC-2011','dd-mon-yyyy') - to_date('03-NOV-2011','dd-mon-yyyy') + 1
) d
left join (
    select 
        currentdate,
        count(distinct(grauser_id)) as dist_ucnt,
        eventdesc 
    from
        txn_summ_dec

    group by currentdate, eventdesc 
) td on td.currentdate = d.dateInRange order by d.dateInRange asc

Resultset:
Date                    eventdesc       dist_cnt
2011-11-03 00:00:00     null                0
and so on..
2011-11-30 00:00:00     null                0
2011-12-01 00:00:00     Save Object         182
....
2011-12-31 00:00:00     null                0

Upvotes: 0

Views: 2687

Answers (1)

Anthony Accioly
Anthony Accioly

Reputation: 22471

This logic should take care of generating the range:

public static List<Date> dayRange(Date begin, Date end) {
    if (end.before(begin)) {
        throw new IllegalArgumentException("Invalid range");
    }

    final List<Date> range = new ArrayList<>();
    final Calendar c1 = extractDate(begin);
    final Calendar c2 = extractDate(end);

    while (c1.before(c2)) {
        range.add(c1.getTime()); // begin inclusive
        c1.add(Calendar.DATE, 1);
    }
    range.add(c2.getTime()); // end inclusive

    return range;
}

private static Calendar extractDate(Date date) {
    final Calendar c = Calendar.getInstance();

    c.setTime(date);
    c.set(Calendar.HOUR, 0);
    c.set(Calendar.MINUTE, 0);
    c.set(Calendar.SECOND, 0);
    c.set(Calendar.MILLISECOND, 0);

    return c;
}

Not as pretty and terse as Joda Time, but gets you going.

If all you need is to display your query results with zeroes for dates without results, run the original query grouped by date (without the left join) and later fill in missing dates. Assuming that your query returns a Map<String, Long>: 

final DateFormat df = new SimpleDateFormat("d-MMM-yyyy", Locale.US);

final Date begin = df.parse("5-Feb-2011");
final Date end = df.parse("2-March-2011");

final List<Date> range = dayRange(begin, end);
final Map<String, Long> result = // code to execute your query

for (Date date: range) {
    String key = df.format(date);
    if(!result.containsKey(key)) {
        result.put(key, 0l);
    }
}

I hope this helps.

Upvotes: 1

Related Questions