CODEWITHSUNDEEP
c#dotnethighcharts
Grizzly
Grizzly

Reputation: 5943

GroupBy Hour then Average between two points

I have a database that holds records and those records have a datetime. As a request, I have built a timeline to show how many records were recorded for each hour of the day for the entire year.

So, for that I simply grouped by the hour and counted the number of objects in each of those hours:

var lstByHour =
    lstAllRecords.Where(x => x.RecordDateTime.Year == year && !x.deleted)
        .GroupBy(x => x.RecordDateTime.Hour)
        .Select(x => new object[] {x.Count()}).ToArray();

I am using that information to put into a line chart and here is how that looks:

enter image description here

But now, I have been asked to get the half-hour.. or the point that is in between each hour.. so what I was thinking was to take two points and find the average and that would give the middle number between two points. For example.. on the 0 hour that point is 35.. on the 1 hour that point is 41.. so in order to find the number in between, I would just add 35 + 41 = 76 / 2 = 38.. then plot 38 in between the 0 and 1 hour. In the case of dividing odd numbers by 2 and ending up with a decimal.. I would like to round up.

My problem is that I'm not sure on where to start on editing my lambda expression to get that value.. and continuously get that value between each hour.

Any help is appreciated.

UPDATE

Using the duplicate question I'm confused on how my code would look to get 30 minute intervals. I have this:

var groups = lstAllRecords.GroupBy(x =>
    {
        var stamp = x.RecordDateTime;
        stamp = stamp.AddMinutes(-(stamp.Minute % 30));
        stamp = stamp.AddMilliseconds(-stamp.Millisecond - 1000 * stamp.Second);
        return stamp;
    })
    .Select(g => new object[] {g.Count()})
    .ToArray();

This is resulting in over 6000 records and the line chart isn't loading.

UPDATE 2

I've tried both the accepted answer and the second answer since they both have answer for 5 minute intervals and it's not working for me..

var grouped = from s in lstAllRecords.Where(x => x.RecordDateTime.Year == year && !x.deleted)
              group s by new DateTime(s.RecordDateTime.Year, s.RecordDateTime.Month,
                    s.RecordDateTime.Day, s.RecordDateTime.Hour, s.RecordDateTime.Minute / 2, 0) into g
              select new object[]{ g.Count() };

Here is my data model

public partial class DailyRecord
{
    public int ID { get; set; }
    public System.DateTime RecordDateTime { get; set; }
    public string IncidentNumber { get; set; }
    public string Summary { get; set; }
    public bool deleted { get; set; }
}

Upvotes: 4

Views: 126

Answers (2)

Evk
Evk

Reputation: 101443

You can group like this:

lstAllRecords.Where(x => x.RecordDateTime.Year == year && !x.deleted)
    .GroupBy(x => x.RecordDateTime.Hour + (x.RecordDateTime.Minute >= 30 ? 0.5 : 0))
    .Select(x => new object[] { x.Count() }).ToArray();

That way all times between 0 and 29 minute will go to the hour group (for example: 1:20 goes to the group of hour 1), and times between 30 and 59 go to the half-hour group (for example: 1:40 goes to the group of 1.5 hours).

If you want for times to go to the closest half-hour group (for example: 1:50 > group of hour 2, 1:35 > group of hour 1.5), then group like this:

lstAllRecords.Where(x => x.RecordDateTime.Year == year && !x.deleted)
    .GroupBy(x => x.RecordDateTime.Hour + (Math.Round(x.RecordDateTime.Minute / 30f, MidpointRounding.AwayFromZero) / 2))
    .Select(x => new object[] { x.Count() }).ToArray();

Upvotes: 2

Basile Perrenoud
Basile Perrenoud

Reputation: 4112

If I understand properly, you want your data grouped by hours, but each group start and end on half hours (for example, a group contain data between 08:30 and 09:30)

If this is what you want, then something like this should work:

var lstByHour =
    lstAllRecords.Where(x => x.RecordDateTime.Year == year && !x.deleted)
        .GroupBy(x => {return x.RecordDateTime.Minutes > 30 ? x.RecordDateTime.Hour + 1 : x.RecordDateTime.Hour})
        .Select(x => new object[] {x.Count()}).ToArray();

EDIT After seeing your comment

This should group by half hours

var lstByHour =
    lstAllRecords.Where(x => x.RecordDateTime.Year == year && !x.deleted)
        .GroupBy(x => {return x.RecordDateTime.Minutes > 30 ? x.RecordDateTime.Hour + 0.5 : x.RecordDateTime.Hour})
        .Select(x => new object[] {x.Count()}).ToArray();

Upvotes: 0

Related Questions