Reputation: 5157
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
2014-08-01 07:00:06.163
2014-08-01 07:00:11.873
2014-08-01 07:00:42.623
2014-08-01 07:00:48.363
2014-08-01 07:01:15.243
2014-08-01 07: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 :
7 O Clock : 56 Records
8 O Clock : 90 Records
..
..
etc.
How it can be possible via LINQ ?
Upvotes: 6
Views: 11753
Reputation: 223422
If your column type is DateTime
then you can group by its Hour
property and get count for each hour like:
var query = dt.AsEnumerable()
.GroupBy(row => row.Field<DateTime>("dateAndTime").Hour)
.Select(grp => new
{
Hour = grp.Key,
Count = grp.Count()
});
where dt
is your DataTable
EDIT: The above should work as long as you have data for a single date in your DataTable
, but if you have rows for multiple dates then grouping should be done on Date
as well as Hour
like:
var query = dt.AsEnumerable()
.GroupBy(row => new
{
Date = row.Field<DateTime>("dateAndTime").Date,
Hour = row.Field<DateTime>("dateAndTime").Hour
})
.Select(grp => new
{
Date = grp.Key.Date,
Hour = grp.Key.Hour,
Count = grp.Count()
});
Upvotes: 19
Reputation: 460380
I assume that the column really stores DateTime
s, you can group by Date
+ Hour
:
var hourGroups = dataTable.AsEnumerable()
.Select(row => new { DateAndTime = row.Field<DateTime>("dateAndTime"), Row = row })
.GroupBy(x => new { Date = x.DateAndTime.Date, Hour = x.DateAndTime.Date.Hour });
foreach (var x in hourGroups)
Console.WriteLine("Date: {0} Hour: {1}: Count: {2} All names: {3}",
x.Key.Date.ToShortDateString(),
x.Key.Hour,
x.Count(),
string.Join(",", x.Select(xx => xx.Row.Field<string>("name")))); // just bonus
Upvotes: 4