CODEWITHSUNDEEP
c#linq
Harshit
Harshit

Reputation: 5157

C# LINQ get records from datatable group by 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

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

Answers (2)

Habib
Habib

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

Tim Schmelter
Tim Schmelter

Reputation: 460380

I assume that the column really stores DateTimes, 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

Related Questions