Java / SQL -How to group time in intervals of 30 minutes or 1 hour

Question

I need a solution to group the times of entry and the display the respective time interval and count of users/records during that time interval eg

Time Interval Count

07:00 AM-07:30 24

12:30 PM-13:00 26

How can we group time based on interval in Java or SQL

I tried group by in sql and LocalTime grouping by using streams But i m not able to segregate based on the time interval

Time Interval Count

07:00:34 12

12:30:23 14

07:23:53 12

12:46:21 12

I m able to get data in the above format only using sql/java(tried both ways)

Answer 1

Here are the code snippets for classic (algorithmic) approach and the second one that uses java streams api:

// algorithmic approach
LocalDateTime[] times = Array.randomLocalDateTimes(5);
Array.print(times);

Map<Integer, Integer> counts = new HashMap<>();
for (LocalDateTime time : times) {
    int hour = time.get(ChronoField.HOUR_OF_DAY);
    int count = counts.getOrDefault(hour, 0);
    counts.put(hour, count+1);
}
System.out.println(counts);


// java streams aproach
Map<Integer, Integer> counts2 = Arrays.stream(times)
     .collect(Collectors.toMap(time -> time.get(ChronoField.HOUR_OF_DAY), hour -> 1, Integer::sum));
System.out.println(counts2);


// java streams for 30 minutes
Map<String, Integer> counts3 = Arrays.stream(times)
     .collect(Collectors.toMap(time -> time.get(ChronoField.HOUR_OF_DAY) + ":" + (time.get(ChronoField.MINUTE_OF_HOUR) < 30 ? "00" : "30"),
                        interval -> 1,
                        Integer::sum));
System.out.println(counts3);

Output:

2022-10-25T18:06:14.245215, 2022-10-25T22:15:14.246607, 2022-10-25T19:29:14.246624, 2022-10-25T18:08:14.246635, 2022-10-25T10:21:14.246645
{18=2, 19=1, 22=1, 10=1}
{18=2, 19=1, 22=1, 10=1}
{10:00=1, 22:00=1, 19:00=1, 18:00=2}

Answer 2

Assume you have different times (whatever the datatype) and need to aggregate over 24 hours/day. It just means 24 buckets to host.

Initialize an array of 24 integers to contain all zeros. The loop over your times. For each of them just drop the minutes so you are left with the hour-of-day. Find the according int in your array and increase the value.

Finally just print your array and you see how many times you had in each hour of the day.

Now when you want to have 30 minute intervals, just increase the number of buckets to 48 and instead of dropping the minutes decide whether it is in the first or second half of the hour.