How to group the data by the datetime intervals？

Question

I am using asp.net core web api + entityframework core 5.0.

there is a table :

Id  datetime                                    value
0  2021-07-08 14:46:46.                      1
1  2021-07-08 14:46:47                       5
2  2021-07-08 14:46:48                       2
3  2021-07-08 14:46:49                       4
4  2021-07-08 15:30:01                        7
5  2021-07-08 15:30:46                        4
6  2021-07-08 15:30:46                       4
7  2021-07-08 15:50:04                       4
8  2021-07-08 15:50:05                      6

how to group them to 3 groups?
because the datetime have intervals over x minutes?
14:46:46 to 14:46:49 us group 1
15:30:01 to 15:30:46 is group 2
15:50:04 to 15:50:05 is group 3

how to group it?

Answer 1

Hi adminj,
According to your situation, I have a suggestion you can refer to.
You can group by minutes, here is a test you can refer to.

var Counter = new List<CounterData>  
{  
    new CounterData {Id=0,datetime =new DateTime( 2021,07,08,14,46,46), value = 1 },  
    new CounterData {Id=1,datetime =new DateTime( 2021,07,08,14,46,47), value = 5 },  
    new CounterData {Id=2,datetime =new DateTime( 2021,07,08,14,46,48), value = 2 },  
    new CounterData {Id=3,datetime =new DateTime( 2021,07,08,14,46,49), value = 4 },  
    new CounterData {Id=4,datetime =new DateTime( 2021,07,08,15,30,01), value = 7 },  
    new CounterData {Id=5,datetime =new DateTime( 2021,07,08,15,30,46), value = 4 },  
    new CounterData {Id=6,datetime =new DateTime( 2021,07,08,15,30,46), value = 4 },  
    new CounterData {Id=7,datetime =new DateTime( 2021,07,08,15,50,04), value = 4 },  
    new CounterData {Id=8,datetime =new DateTime( 2021,07,08,15,50,05), value = 6 },  
};  
  
  
TimeSpan interval = new TimeSpan(0, 1, 0);  // 1 minutes.  
var groupedTimes = from dt in Counter                               
                 group dt by dt.datetime.Ticks / interval.Ticks  
                into g  
                    select new { minute = new DateTime(g.Key * interval.Ticks), Values = g.Count() };  
foreach (var value in groupedTimes)  
{  
    Console.WriteLine(value.minute);  
    Console.WriteLine("\t{0}", String.Join(", ", value.Values));  
}  

The result:

Best Regards,
Daniel Zhang

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.