GroupBy Hour then Average between two points

Question

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:

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; }
}

How are the data stored? Is there data for every minute? Every hour? — Heretic Monkey, Mar 06 '18 at 13:47
@MikeMcCaughan When the user's create records that are able to select the date, hour and minute.. no second — Grizzly, Mar 06 '18 at 13:47
I would just not group by anything and use your charting tool to show increments of every half hour... assuming your charting tool can do that (there's no tag or information about what you're using there). — Heretic Monkey, Mar 06 '18 at 13:54
@MikeMcCaughan sorry about that.. I have added the necessary tag and it is highcharts, but I am creating the chart on the backend.. not in Javascript. — Grizzly, Mar 06 '18 at 13:56
Possible duplicate of [LINQ aggregate and group by periods of time](https://stackoverflow.com/questions/8856266/linq-aggregate-and-group-by-periods-of-time) — Heretic Monkey, Mar 06 '18 at 13:58
@MikeMcCaughan see my edit on how I'm trying to understand how to use that accepted answer to fit my needs. — Grizzly, Mar 06 '18 at 14:01
Do `g.Count()` instead of `g.Average()`, since that's apparently what you want according to your original code... — Heretic Monkey, Mar 06 '18 at 14:03
@MikeMcCaughan edited my code to show what I have now, and it is returning over 6000 records.. which can't be right.. — Grizzly, Mar 06 '18 at 14:07
Couldn't tell you what's going on, since you are not sharing your data model. All we can do is guess. Note also that you are allowed to look at answers other than the accepted answer in the duplicate. — Heretic Monkey, Mar 06 '18 at 14:09
@MikeMcCaughan edited question to show my model.. I've tried two answers on the duplicate and they aren't providing correct results — Grizzly, Mar 06 '18 at 14:20

score 2 · Accepted Answer · answered Mar 06 '18 at 15:04

2

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();

answered Mar 06 '18 at 15:04

Evk

98,527
8
141
191

Okay, so I have used the first suggestion.. and it is working as expected.. I just want to ensure I'm understanding correctly.. as an example now.. on the `0` hour I have a count of 19.. so that means all records between 0000 and 0029.. then on `0.5` I have a count of 20.. so that means all records between 0030 and 0059.. then on `1` I have a count of 142.. so that's all records between 0100 and 0129? I guess my question is.. when I grouped by hour.. the count for hour `0` was 35.. but on your answer 19+20 = 39 – Grizzly Mar 06 '18 at 15:17
@GTown-Coder I don't see how it can happen. Maybe worth rechecking again that you do this (by hour and by half-hour) on exactly the same data. – Evk Mar 06 '18 at 15:25
you don't think doing this by half hour is possible? – Grizzly Mar 06 '18 at 15:27
@GTown-Coder I mean I don't see how grouping by hour can return 35 for hour 0, but grouping by half hour can return 19+20. But grouping by half-hour is certainly possible, as described in the answer :) And your understanding about how it works is correct. – Evk Mar 06 '18 at 15:28
Okay, I'm in the database now.. I did this SELECT * FROM [DataBaseName].[dbo].[DailyRecord] Where YEAR(RecordDateTime) = 2017 AND deleted = 0 AND DATEPART(HOUR, RecordDateTime) = 00.. and that returned 70 records.. what am I doing wrong in not getting the correct count... – Grizzly Mar 06 '18 at 15:38
Nvmd.. so apparently the object `lstAllRecords` wasn't being updated for some reason so I swapped that out for `db.DailyRecords....` and it worked. – Grizzly Mar 06 '18 at 15:43
So after all that does what you need? – Evk Mar 06 '18 at 15:45
Yes it does! Thank you!! – Grizzly Mar 06 '18 at 15:46
Sorry, but is my question really considered a duplicate since your answer and the accepted answer on the other question are different? – Grizzly Mar 06 '18 at 15:46
@GTown-Coder I personally don't think it's duplicate, and it seems community doesn't too, since there is only one close vote. – Evk Mar 06 '18 at 15:50
Anyway to get rid of the duplicate tag then? – Grizzly Mar 06 '18 at 15:50
There is no duplicate tag (otherwise I won't be able to post an answer), only one close vote (and you need 5 such votes for question to be closed as duplicate). Try refreshing your page. – Evk Mar 06 '18 at 15:52

Basile Perrenoud · Answer 2 · 2018-03-06T15:04:33.227

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();

Actually, I would like my data grouped by half-hour increments. — Grizzly, Mar 06 '18 at 14:40

GroupBy Hour then Average between two points

2 Answers2