0

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)

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
Gnanambal Meyyappan
  • 23
  • 1
  • 4
  • Is there any date or time zone involved or implied? – Basil Bourque Oct 25 '22 at 17:05
  • I changed your examples to 24 hour clock for simplicity, less distraction. – Basil Bourque Oct 25 '22 at 17:06
  • What *exactly* is the data type of your column with the time? What database? – Basil Bourque Oct 25 '22 at 17:07
  • And when you say 'I tried to group by in sql' - show us the SQL, or hibernate, or whatever you do already have. – rzwitserloot Oct 25 '22 at 17:11
  • @rzwitserloot please find the querySELECT max(convert(varchar,(CAST (DATEADD(SECOND,signin_timestamp/1000 ,'1970/1/1') AS datetime)),8)) as session_start_date, count(*) as number_of_sessions FROM [dbo].[user_attendance_information] WHERE (CAST (DATEADD(SECOND,signin_timestamp/1000 ,'1970/1/1') AS date)) = '2022-08-17' GROUP by datepart(hour, (CAST (DATEADD(SECOND,signin_timestamp/1000 ,'1970/1/1') AS smalldatetime))), datepart(minute, (CAST (DATEADD(SECOND,signin_timestamp/1000 ,'1970/1/1') AS smalldatetime))); – Gnanambal Meyyappan Oct 26 '22 at 04:49

2 Answers2

1

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.

Queeg
  • 7,748
  • 1
  • 16
  • 42
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}
c3R1cGFy
  • 505
  • 4
  • 12