Reputation: 11
I have a datatable whose columns are name, code, dateAndTime. Now I want to get the count of all the records for every hour in a Day using LINQ.
DateTime Column contains data as
2018-08-01 07:00:06.163
2018-08-01 07:50:11.873
2018-08-01 08:00:42.623
2018-08-01 07:20:48.363
2018-08-01 09:01:15.243
2018-08-01 06:01:16.507
Now I want to get the count of all the records from the start of the day to the end of the day hourly basis.
Example would be:
if first record is around 5 Am on 2018-08-01 then its start with 5 AM to 7 Am as first row
2018-08-01 7 ( 5 Am - 7 Am) - 36 count
2018-08-01 9 ( 7 Am - 9 Am) - 12 count
2018-08-01 11 ( 9 Am - 11 Am) - 12 count
2018-08-01 13 (11 Am - 1 PM)- 0 count
2018-08-01 15 (1 PM - 3 PM)- 36 count
Upvotes: 0
Views: 140
Reputation: 157
You can try out this piece of code:
const int groupHours = 2;
const int driftHours = 1;
var dateTimes = new List<DateTime>() { new DateTime(2018, 8, 30, 7, 0, 6), new DateTime(2018, 8, 30, 8, 0, 6) };
var result = dateTimes.GroupBy(d => new DateTime(d.Year, d.Month, d.Day, (((d.Hour - driftHours) / groupHours) + 1) * groupHours + driftHours, 0, 0)).Select(g => new { Key = g.Key, Count = g.Count() });
Example of (((d.Hour - driftHours) / groupHours) + 1) * groupHours + driftHours
for 8 AM:
Upvotes: 1