Aggregate values at hourly interval using LINQ C#

Question

I have the following JSON result from API .

[
    {
        "ID": "1",
        "Value" : "10",
        "TimeStamp": "2019-10-21 00:00:00"

    },
    {
        "ID": "1",
        "Value": "10",
        "TimeStamp": "2019-10-21 00:15:00"

    },
    {
        "ID": "1",
        "Value": "10",
        "TimeStamp": "2019-10-21 00:30:00"
    },
    {
        "ID": "1",
        "ResultValue": "10",
        "TimeStamp": "2019-10-21 00:45:00"
    },
    {
        "ID": "1",
        "Value": "20",
        "TimeStamp": "2019-10-21 01:00:00"

    },
    {
        "ID": "1",
        "Value": "10",
        "TimeStamp": "2019-10-21 01:15:00"

    },
    {
        "ID": "1",
        "Value": "10",
        "TimeStamp": "2019-10-21 01:30:00"
    },
    {
        "ID": "1",
        "Value": "10",
        "TimeStamp": "2019-10-21 01:45:00"
    },
    {
        "ID": "1",
        "Value": "30",
        "TimeStamp": "2019-10-21 02:00:00"
    }
]

I am trying to show the hourly aggregation using below LINQ query

var aggList = items.GroupBy(u => new { u.ID, u.TimeStamp.Date, u.TimeStamp.Hour })
                            .Select(g => new TestData
                            {
                                ID = g.Key.ID,
                                TimeStamp = g.Key.Date.AddHours(g.Key.Hour),
                                Value = g.Sum(k => k.Value)
                            }).ToList();

Which gives result as

  ID : 1
    TotalValue : 40
    TimeStamp : 21.10.2019 00:00:00

    ID : 1
    TotalValue : 50
    TimeStamp : 21.10.2019 01:00:00

What should be my LINQ query , If i want the hourly aggregation to consider from 15th minute instead of 0th minute .

Like first hour should have aggregation of

2019-10-21 00:15:00 - 10
2019-10-21 00:30:00 - 10
2019-10-21 00:45:00 - 10
2019-10-21 01:00:00 - 20

And the expected result should be

        ID : 1
        TotalValue : 50
        TimeStamp : 21.10.2019 00:00:00

        ID : 1
        TotalValue : 60
        TimeStamp : 21.10.2019 01:00:00

related: https://stackoverflow.com/questions/57112533/i-want-combine-my-time-slots-as-per-interval-15-30-45-60-and-do-the-additon-o/57114698#57114698 — xdtTransform, Nov 19 '19 at 08:55

Emanuele · Answer 1 · 2019-11-19T09:40:15.643

4

var aggList = items.GroupBy(u => new { u.ID, u.TimeStamp.AddMinutes(-15).Date, u.TimeStamp.AddMinutes(-15).Hour })
                            .Select(g => new TestData
                            {
                                ID = g.Key.ID,
                                TimeStamp = g.Key.Date.AddHours(g.Key.Hour),
                                Value = g.Sum(k => k.Value)
                            }).ToList();

edited Nov 19 '19 at 09:40

answered Nov 19 '19 at 08:57

Emanuele

723
4
15

This shift the first 15 minutes of the day away to the 23th hour of that day, while it should actually belong to the day earlier. – tia Nov 19 '19 at 09:28

score 2 · Accepted Answer · answered Nov 19 '19 at 09:00

You can shift your Timestamp to get the effect. Instead of

items.GroupBy(u => new { u.ID, u.TimeStamp.Date, u.TimeStamp.Hour })

You shift the time that used for grouping by to be 15 minutes in the past, so everything at the first 15 minutes of the hour go to the earlier hour.

items.GroupBy(u => new { u.ID, u.TimeStamp.AddMinutes(-15).Date, u.TimeStamp.AddMinutes(-15).Hour })

score 0 · Answer 3 · answered Nov 19 '19 at 09:28

"If i want the hourly aggregation to consider from 15th minute instead of 0th minute". You can add 15 minutes to the hour and group them after that.

public class ApiResult
{
    public int ID { get; set; }
    public int Value { get; set; }
    public DateTime TimeStamp { get; set; }
}

var result = JsonConvert.DeserializeObject<IList<ApiResult>>(File.ReadAllText("../../../data.json"));

var AggValues = result.GroupBy(x => new
{
    x.ID,
    x.TimeStamp.AddMinutes(15).Date,
    x.TimeStamp.AddMinutes(15).Hour,
    x.Value
}).Select(x => new ApiResult
{
    ID = x.Key.ID,
    TimeStamp = x.Key.Date.AddHours(x.Key.Hour),
    Value = x.Sum(sum => sum.Value)
});

foreach(var i in AggValues)
{
    Console.WriteLine(i.ID + " " + i.Value + " " + i.TimeStamp);
}

Output:

1 30 2019-10-21 00:00:00
1 0 2019-10-21 01:00:00
1 20 2019-10-21 01:00:00
1 20 2019-10-21 01:00:00
1 10 2019-10-21 02:00:00
1 30 2019-10-21 02:00:00

Aggregate values at hourly interval using LINQ C#

3 Answers3