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}