CODEWITHSUNDEEP
c#linq
Subhash Tripathi
Subhash Tripathi

Reputation: 11

Get records from datatable grouped by date and every two hour

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

Answers (1)

Szymon Chęciński
Szymon Chęciński

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:

  • substract 1 (hours to drift from regular numbers divisable by 2) from 8 AM = 7 AM
  • divide by 2 (hour span in group) and cut off everything after decimal point - how many "groups" there were to this point
  • add 1 to previous result to get the current group
  • multiply by 2 (hour span in group) to get hour for this group
  • add 1 (drift) to make 9 out of 8, 11 out of 10 etc

Upvotes: 1

Related Questions