Get total timespan for each key in the table

Question

I have a data table with the following data:

LINE | SIGN | DATE
-----------------------------------------
   1 |   1  |  2015-03-02 11:23:25
   1 |   1  |  2015-03-02 18:24:03
   1 |   1  |  2015-03-03 05:38:49
   1 |   0  |  2015-03-03 08:47:02
   1 |   1  |  2015-03-03 14:01:31
   1 |   1  |  2015-03-03 21:11:53
   1 |   1  |  2015-03-04 09:34:04
   1 |   0  |  2015-03-04 15:29:27
   1 |   0  |  2015-03-04 19:28:33

The data is ordered by date as you can see, my requirement is to get the Total number of minutes for a signal. Like for how many minutes the signal was 1 and for how many minutes the signal was 0.

I need a LINQ query to get the results.

At the moment what I am thinking is that I may have to do a for loop and add the number of minutes. I don't know if this is the only way:

int noOfMinutesFor1 = 0;
for (int i = 1; i < signalData.Count; i++)
{
    if((signalData[i - 1].SIGN == signalData[i].SIGN == 1) || (signalData[i].SIGN == 1 && signalData[i - 1].SIGN == 0))
    {
        noOfMinutesFor1 += (signalData[i].SIGN - signalData[i - 1].SIGN).TotalMinutes;
    }

}

http://stackoverflow.com/questions/12521366/getting-time-span-between-two-times-in-c — pijemcolu, Jun 22 '16 at 08:49
*I need a LINQ query to get the results.* If you need something, don't ask us but do it. — Gert Arnold, Jun 22 '16 at 08:50
I know how to get TimeSpan, I want the difference between the times in the data above. — progrAmmar, Jun 22 '16 at 08:51
@GertArnold dude, atleast nudge me in the right direction, I want to do this efficiently, but the only way I see is going through each row and check if the signal has changed or not and add the number of minutes to a variable. I need to know if it can be done any other way — progrAmmar, Jun 22 '16 at 08:53
I do not have much details about your requirement, but would it be a solution to sum up the time for when the signal was 1 and extract the total minutes it stayed that way - and do the same for when the signal was 0 ? — Veverke, Jun 22 '16 at 08:56
You've been around for a couple of years. You should know by now that it's almost a law of physics that at Stack Overflow you always your own efforts. Why? Because they clarify more than you might think (for example, you don't tell what kind of LINQ you're using. Plain to objects, to SQL, ...?) and because the SO community is particularly allergic to the do-my-work-for-me type of questions. — Gert Arnold, Jun 22 '16 at 08:58
@GertArnold boy you sure woke up on the wrong side of the bed. I don't need anyone to 'do-my-work' I am simply asking to give me a logic for that. You seriously didn't have to write any code there, look what Veverke's response is. I might be missing something in it that's why I put the question here so that I may have a second opinion — progrAmmar, Jun 22 '16 at 09:00
@GertArnold: believe I (and anyone else) can understand your point, but pointing things this way is absolutely way out of proportion. This is after all a public web site, that welcomes people that want to do this or that. The guy shared some input, his question may not be the best one out there but - anything other than that is pure (and not pleasant at all) exaggeration. — Veverke, Jun 22 '16 at 09:02
@Veverke Thanks for the input, I have an issue with adding the minutes when 1 and 0, the thing is that the date times are changing so if for example the last signal was 0 yesterday and then today, it might sum up to 24 hours. I am going to edit the question to explain a bit more — progrAmmar, Jun 22 '16 at 09:07
I am trying to create a sample of what I intended so I can share. — Veverke, Jun 22 '16 at 09:14
`signalData[i - 1].SIGN == signalData[i].SIGN == 1` <= what on earth is that? — Thomas Ayoub, Jun 22 '16 at 09:23
may I change your question's title ? I think it should be "Get total timespan for each key". I think this is what you want, it would be clearer. — Veverke, Jun 22 '16 at 09:30

Veverke · Accepted Answer · 2016-06-22T09:26:08.297

This is what I would do:

The idea is grouping all dates per signal, afterwards, upon sorting the dates in descending order, subtract each pair and keep the offset hour and minutes (in minutes) aside.

 public static void Example()
{
    DataTable dt = new DataTable();

    dt.Columns.Add("Signal", typeof(int));
    dt.Columns.Add("Date", typeof(DateTime));

    dt.Rows.Add(1, DateTime.ParseExact("2015-03-02 11:23:25", "yyyy-MM-dd HH:mm:ss", CultureInfo.CurrentCulture));
    dt.Rows.Add(1, DateTime.ParseExact("2015-03-02 18:24:03", "yyyy-MM-dd HH:mm:ss", CultureInfo.CurrentCulture));
    dt.Rows.Add(1, DateTime.ParseExact("2015-03-03 05:38:49", "yyyy-MM-dd HH:mm:ss", CultureInfo.CurrentCulture));
    dt.Rows.Add(0, DateTime.ParseExact("2015-03-03 08:47:02", "yyyy-MM-dd HH:mm:ss", CultureInfo.CurrentCulture));
    dt.Rows.Add(1, DateTime.ParseExact("2015-03-03 14:01:31", "yyyy-MM-dd HH:mm:ss", CultureInfo.CurrentCulture));
    dt.Rows.Add(1, DateTime.ParseExact("2015-03-03 21:11:53", "yyyy-MM-dd HH:mm:ss", CultureInfo.CurrentCulture));
    dt.Rows.Add(1, DateTime.ParseExact("2015-03-04 09:34:04", "yyyy-MM-dd HH:mm:ss", CultureInfo.CurrentCulture));
    dt.Rows.Add(0, DateTime.ParseExact("2015-03-04 15:29:27", "yyyy-MM-dd HH:mm:ss", CultureInfo.CurrentCulture));
    dt.Rows.Add(0, DateTime.ParseExact("2015-03-04 19:28:33", "yyyy-MM-dd HH:mm:ss", CultureInfo.CurrentCulture));

    var groups = dt.AsEnumerable().GroupBy(r => (int)r["Signal"]);

    foreach (var group in groups)
    {
        int groupMinutes = 0;
        var datesDescending = group.OrderByDescending(g => g["Date"]);
        for (int i = 0; i < datesDescending.Count(); i += 2)
        {
            var date1 = (DateTime)datesDescending.ElementAt(i)["Date"];
            if (datesDescending.Count() > i + 1)
            {
                var date2 = (DateTime)datesDescending.ElementAt(i + 1)["Date"];
                var dateOffset = date1.Subtract(date2);
                groupMinutes += dateOffset.Hours * 60 + dateOffset.Minutes;
            }
            else
                groupMinutes += date1.Hour * 60 + date1.Minute;
        }

        Console.WriteLine("Signal: {0}, total minutes: {1}", group.Key, groupMinutes);
    }
}

Output:

Thanks Veverke I will try and let you know – progrAmmar Jun 22 '16 at 09:45 — progrAmmar, Jun 22 '16 at 09:45

Get total timespan for each key in the table

1 Answers1