Find Gaps in Two Date Columns which have Overlapping Dates and Hours

Question

Problem - I have a list of two DateTime Columns in which I want to find the gaps in Time between the two columns where the Dates overlap but there are gaps between the individual dates and hours.

I've been experimenting with the TimePeriod library, but it is not able to discern between the actual gaps within the overlapping periods.

Date Range Example. The start date range is 3/1/2024. The end date range is 6/1/2024 A sample gap exists between 4/1/2024 and 5/1/2024 lines 1 and 5. The time between 1 AM and 5 AM is missing from 4/1 to 5/1.

     var organizeCurvePoints = new List<CurveDateOrganizer>();
                    foreach (var d in AllCurvePointsInRangeCopy)
                    {
                        organizeCurvePoints.Add(new CurveDateOrganizer( d.LocalEffectiveTime, d.LocalTerminationTime));
                    }
        
                    organizeCurvePoints.OrderBy(t => t.EffectiveTime);
        
                    TimePeriodCollection effectiveHours = new TimePeriodCollection();
                    effectiveHours.Add(new TimeRange(startDate, endDate));
        
                    // Range of Time in PeriodorganizeCurvePoints
                    ITimePeriodCollection effectivePeriod = new TimePeriodCombiner<TimeRange>().CombinePeriods(effectiveHours);
        
                    // Define All Records in Period
                    TimePeriodCollection curvePeriodDates = new TimePeriodCollection();
        
                    foreach (var day in AllCurvePointsInRangeCopy)
                    {
                        curvePeriodDates.Add(new TimeRange(day.LocalEffectiveTime, day.LocalTerminationTime));
                    }
        
                    ITimePeriodCollection changePeriods = new TimePeriodCombiner<TimeRange>().CombinePeriods(organizeCurvePoints);
        
                    TimePeriodCollection gaps = new TimePeriodCollection();
                    foreach (ITimePeriod basePeriod in effectiveHours)
                    {
                        gaps.AddAll(new TimeGapCalculator<TimeRange>().GetGaps(changePeriods, basePeriod));
                    }

// Class to Hold Dates

[Serializable]
    [DebuggerDisplay("EffectiveTime {EffectiveTime} TerminationTime {TerminationTime}",  Name = "{DateOrganizer}")]
    public class CurveDateOrganizer
    {

        private DateTime _effectiveTime;


        public DateTime EffectiveTime
        {
            get => _effectiveTime;
            set
            {
                if (_effectiveTime != value)
                {
                    _effectiveTime = value;                   
                }
            }
        }

        private DateTime _terminationTime;

        public DateTime TerminationTime
        {
            get => _terminationTime;
            set
            {
                if (_terminationTime != value)
                {
                    _terminationTime = value;                   
                }
            }
        }

       public CurveDateOrganizer(DateTime localEffectiveTime, DateTime localTerminationTime)
        {
            _effectiveTime = localEffectiveTime;
            _terminationTime = localTerminationTime;
        }

    }

Assuming these are all in the same time zone (e.g., UTC) you might find it easier to convert the dates to numbers (e.g., ToUnixTimeSeconds()) and then use simple maths to find any gaps. — Kane
– Kane, Commented May 15, 2023 at 23:29

Kane · Accepted Answer · 2023-05-16 01:19:24Z

0

Assuming these are all in the same time zone (e.g., UTC) you might find it easier to convert the dates to numbers (e.g., ToUnixTimeSeconds()) and then use code similar to the below to find the gaps or overlaps.

using System;
using System.Collections.Generic;

public class Program
{
    public static void Main()
    {
        // Array of number ranges (min & max or start & end)
        long[,] ranges = { { 1, 5 }, { 3, 7 }, { 10, 15 }, { 12, 20 } };

        Console.WriteLine("Gaps in the number ranges:");
        foreach (long gap in FindGapsInRanges(ranges))
        {
            Console.WriteLine(gap);
        }

        Console.WriteLine("Overlaps in the number ranges:");
        foreach (long overlap in FindOverlapsInRanges(ranges))
        {
            Console.WriteLine(overlap);
        }
    }

    public static IEnumerable<long> FindGapsInRanges(long[,] ranges)
    {
        long numRanges = ranges.GetLongLength(0);

        for (long i = 0; i < numRanges - 1; i++)
        {
            long rangeEnd = ranges[i, 1];
            long nextRangeStart = ranges[i + 1, 0];

            if (rangeEnd < nextRangeStart)
            {
                for (long gap = rangeEnd + 1; gap < nextRangeStart; gap++)
                {
                    yield return gap;
                }
            }
        }
    }

    public static IEnumerable<long> FindOverlapsInRanges(long[,] ranges)
    {
        long numRanges = ranges.GetLongLength(0);

        for (long i = 0; i < numRanges - 1; i++)
        {
            long rangeEnd = ranges[i, 1];
            long nextRangeStart = ranges[i + 1, 0];

            if (rangeEnd >= nextRangeStart)
            {
                for (long overlap = nextRangeStart; overlap <= rangeEnd; overlap++)
                {
                    yield return overlap;
                }
            }
        }
    }
}

Running the above code returns the following result:

Gaps in the number ranges:
8
9
Overlaps in the number ranges:
3
4
5
12
13
14
15

answered May 16, 2023 at 1:19

Kane

16.8k11 gold badges67 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2284452 Over a year ago

Elegant solution Kane, however, the algorithm does not recognize the gaps in the UnixTime values overlap. In your example, there is no overlap, e.g. long[,] ranges = { { 1, 5 }, { 3, 7 }, { 10, 15 }, { 12, 20 } };. My UnixTime values over lap, but gaps exist in the overlap. 1709272800 1711962000 1709334000 1714536000 1709290800 1717243200 1709301600 1717275600 1714539600 1717232400

user2284452 Over a year ago

long[,] unixRanges = { { 1709272800, 1711962000 }, { 1709334000, 1714536000 }, {1709290800, 1717243200 }, { 1709301600, 1717275600 }, { 1714539600, 1717232400 } };

user2284452 Over a year ago

Kane - FYI, the below falls into an infinite loop as it tries to cycle through the numbers. Console.WriteLine("Overlaps in the number ranges:"); foreach (long overlap in CurveDateOrganizer.FindOverlapsInRanges(unixDateRanges)) { Console.WriteLine(overlap); }

Collectives™ on Stack Overflow

Find Gaps in Two Date Columns which have Overlapping Dates and Hours

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related