Spring Data JPA - building a query before and after date

Question

I am new to Java JPA - Spring Boot. I want to create a JPA lookup that will count the number of rows between two dates.

I need to get a number that shows the number of members that have joined in the last 28 days. So like 10.

But also get a value that indicates the difference from last month -- so +4% -- or -2% -- so I suppose it will be a count1/count2 * 100 = difference. How would you detect the polarity -- so assess if its negative or positive?

Currently I have something like this

long countByRegisteredDateAfter(Date thresholdDate) throws Exception;

but need something maybe more like this

long countByRegisteredBeforeDateAfter(Date thresholdDate1, Date thresholdDate2)
    throws Exception;

and maybe something more fine tuned like this

long countByRegisteredBeforeAndDateAfterAndRole(Date thresholdDate1, Date thresholdDate2, String role)
    throws Exception;

Code so far:

            // 28 days ago
            Calendar thresholdPast28 = Calendar.getInstance();
            thresholdPast28.set(Calendar.HOUR_OF_DAY,0);
            thresholdPast28.set(Calendar.MINUTE,0);
            thresholdPast28.set(Calendar.SECOND,0);
            thresholdPast28.add(Calendar.DATE,-28);

            java.util.Date thresholdPast28Date = thresholdPast28.getTime();
            Long countLast28Days = (Long) tblLoginRepository.countByRegisteredDateAfter(thresholdPast28Date);

            System.out.println("countLast28Days " + countLast28Days);


            // 56 days ago
            Calendar thresholdPast56 = Calendar.getInstance();
            thresholdPast56.set(Calendar.HOUR_OF_DAY,0);
            thresholdPast56.set(Calendar.MINUTE,0);
            thresholdPast56.set(Calendar.SECOND,0);
            thresholdPast56.add(Calendar.DATE,-28);

            java.util.Date thresholdPast56Date = thresholdPast56.getTime();
            Long countLast56Days = (Long) tblLoginRepository.countByRegisteredDateAfter(thresholdPast56Date);

            System.out.println("countLast56Days " + countLast56Days);

Answer 1

I'm a bit confused as the topic states that you want to try to find dates before and after while in later you want to have them between. Nevertheless to get dates between try:

long countByRegisteredDateBetween(Date thresholdDate1, Date thresholdDate2)

or in the second example

long countByRegisteredDateBetweenAndRole(Date thresholdDate1, Date thresholdDate2, String role)

and to get before and after try something like:

long countByRegisteredDateBeforeAndRegisteredDateAfter(Date thresholdDate1, Date thresholdDate2)

similar do with the Role case