GroupBy DateTime column with offset C# Linq

Question

In my .NET 4.5.2 C# console app, I have a List<SomeItem> called someItems with SomeItem being:

public class SomeItem    {
    public int A { get; set; }
    public int B { get; set; }
    public DateTime C { get; set; }
}

I then group these items into a new list based on A and B:

var groupedItems = someItems.GroupBy(x => new { x.A, x.B });

I am now looking to introduce C into the GroupBy, but with a catch: I would like to group together SomeItems whose C property is within +- (plus or minus) of 2 seconds from each other (essentially a 2-second offset). So, for example, two SomeItem, one having C set to 2016-03-28 17:58:01.000 and the other to 2016-03-28 17:58:03.000, would be grouped together. Is that possible?

Edit: For all intents and purposes let's assume there won't be items in the list which could cause 'overlapping' groups (like the question by @J. Steen in the comments)

Edit 2: An example

Assuming the following data:

• 15/06/2017 00:00:02
• 15/06/2017 00:00:04
• 15/06/2017 00:00:06
• 15/06/2017 00:00:09
• 15/06/2017 00:00:11
• 15/06/2017 00:00:15

..and an offset of 2 seconds, I would expect them to be grouped in the following manner:

Group 1:

• 15/06/2017 00:00:02
• 15/06/2017 00:00:04
• 15/06/2017 00:00:06

Group 2:

• 15/06/2017 00:00:09
• 15/06/2017 00:00:11

Group 3:

• 15/06/2017 00:00:15

Answer 1

Your example input and output are little confusing.

Your first 3 items 2, 4, and 6 have an overlap. 4 can belong with both 2 and 6. This means to decide on the grouping you need to decide which item the groupings will be base on. If you start the groupings with 2 then the results would be

• Group 1: 2,4
• Group 2: 6,
• Group 3: 9, 11
• Group 4: 15

It seems that your have applied your human brain to see that actually 6 could join the first group if the starting point was 4 seconds. The groups then become:

• Group 1: 2,4, 6
• Group 2: 9, 11
• Group 3: 15

You can create an IEqualityComparer<DateTime> to do this however your grouping is then dependant on the order of the collection:

public class GroupingComparer : IEqualityComparer<DateTime>
{
    private readonly int _offset;

    public GroupingComparer(int offset)
    {
        _offset = offset;
    }

    public bool Equals(DateTime x, DateTime y)
    {
        if (y.Second >= x.Second - _offset && y.Second <= x.Second + _offset) return true;

        return false;
    }

    public int GetHashCode(DateTime obj)
    {
        //Should most probably look at a better way to get the hashcode.
        return obj.ToShortDateString().GetHashCode();
    }
}

using it like this:

GroupingComparer comparer = new GroupingComparer(offset:2);
var result2 = dates.GroupBy(x => x, comparer).ToList(); 

So now it all depends on what you want to do. You can get either of the above outputs by changing the order of the collection. However this could mean strange behaviour in an application if you use different orders at different parts of the application. Maybe an extension method OrderAndGroup could resolve this.

Answer 2

You can create an extension method to round the seconds. I found this example: Have datetime.now return to the nearest second

The answer from that question is:

public static DateTime Trim(this DateTime date, long ticks) {
   return new DateTime(date.Ticks - (date.Ticks % ticks), date.Kind);
}

You can then do something like this in the group by:

var result = dates.GroupBy(x => x.Trim(TimeSpan.FromSeconds(3).Ticks));

So give the inputs

• 15/06/2017 00:00:01
• 15/06/2017 00:00:02
• 15/06/2017 00:00:03
• 15/06/2017 00:00:04
• 15/06/2017 00:00:05
• 15/06/2017 00:00:06

You would have 3 groups:

Group A

1. 15/06/2017 00:00:01
2. 15/06/2017 00:00:02

Group B

1. 15/06/2017 00:00:03
2. 15/06/2017 00:00:04
3. 15/06/2017 00:00:05

Group C:

1. 15/06/2017 00:00:06

Answer 3

The below code should do the trick.

Basically it does the grouping in multiple passes - first on A and B and then it uses TakeWhile and Skip to group the dates in the way that you'd like them to be grouped.

using System;
using System.Collections.Generic;
using System.Linq;

namespace Test
{
    public class SomeItem
    {
        public int A { get; set; }
        public int B { get; set; }
        public DateTime C { get; set; }

        public override string ToString()
        {
            return $"{A} - {B} - {C}";
        }
    }


    public class Program
    {
        static void Main(string[] args)
        {
            // Assuming 2 second margin
            var margin = TimeSpan.FromSeconds(2);

            var input = new List<SomeItem>
            {
                new SomeItem() {A = 1, B = 2, C = new DateTime(2017, 6, 15, 0, 0, 0)},
                new SomeItem() {A = 1, B = 2, C = new DateTime(2017, 6, 15, 0, 0, 2)},
                new SomeItem() {A = 1, B = 2, C = new DateTime(2017, 6, 15, 0, 0, 4)},
                new SomeItem() {A = 1, B = 2, C = new DateTime(2017, 6, 15, 0, 0, 6)},
                new SomeItem() {A = 1, B = 2, C = new DateTime(2017, 6, 15, 0, 0, 9)},
                new SomeItem() {A = 1, B = 2, C = new DateTime(2017, 6, 15, 0, 0, 11)},
                new SomeItem() {A = 1, B = 2, C = new DateTime(2017, 6, 15, 0, 0, 15)},
                new SomeItem() {A = 1, B = 3, C = new DateTime(2017, 6, 15, 0, 0, 2)},
                new SomeItem() {A = 1, B = 3, C = new DateTime(2017, 6, 15, 0, 0, 4)},
                new SomeItem() {A = 1, B = 3, C = new DateTime(2017, 6, 15, 0, 0, 6)},
                new SomeItem() {A = 1, B = 3, C = new DateTime(2017, 6, 15, 0, 0, 9)},
                new SomeItem() {A = 1, B = 3, C = new DateTime(2017, 6, 15, 0, 0, 11)},
                new SomeItem() {A = 1, B = 3, C = new DateTime(2017, 6, 15, 0, 0, 13)}
            };

            var firstGrouping = input.GroupBy(x => new { x.A, x.B });
            var readyForGrouping = new List<Tuple<SomeItem, int>>();

            foreach (var grouping in firstGrouping)
            {
                var data = grouping.OrderBy(z => z.C).ToList();
                var lastDate = default(DateTime?);
                var count = 0;
                var groupingCount = data.Count();
                var groupID = 0;

                while (groupingCount > count)
                {
                    groupID++;
                    readyForGrouping.AddRange(data.Skip(count).TakeWhile(z =>
                    {
                        var old = lastDate;
                        lastDate = z.C;
                        if (old == null)
                        {
                            count++;
                            return true;
                        }
                        if (z.C <= old.Value.Add(margin))
                        {
                            count++;
                            return true;
                        }
                        return false;
                    }).Select(z => new Tuple<SomeItem, int>(z, groupID)).ToList());
                }
            }

            var groupedItems = readyForGrouping.GroupBy(x => new { x.Item1.A, x.Item1.B, x.Item2 },
                x => x.Item1);

            foreach (var grouping in groupedItems)
            {
                Console.WriteLine("Start Of Group");
                foreach (var entry in grouping)
                {
                    Console.WriteLine(entry);

                }
            }
            Console.ReadLine();
        }
    }
}

Answer 4

This is not possible, as GroupBy groups identical items together. You could add a new property that "flattens" times.

So say something like public DateTime FlatDate{get{ return C.ToString("yyyy-MM-dd HH:mm");}}

You could alter the return depending on exactly how you want to group, so if second is < 3 set it to 0, < 6 set it to 3 ect.

Not the most elegant, but will do what you want.