How can I find all data with a given time span

Question

The basic problem is in identifying where sensors in the field have gaps in the data that they are reporting. All of the data is collected and transformed into tables in the data base.

All records have a DateSampleTaken field and once the records are sorted by this field, I would like to know of each instance where the gap between records is (for example) more than 15 minutes. Once the gaps are identified, an object containing the information would be created.

I can certainly do this through iteration, I was just wondering if there is Linq call that would do the trick.

what do you mean by 'between the records'? is it previousRecord - curRecord? — Mrchief, Jun 28 '11 at 21:13

score 6 · Accepted Answer · answered Jun 28 '11 at 21:29

6

Here is a LINQ expression to get a sequence of the samples that have a gap of fifteen minutes to the next sample:

var gaps = samples.Zip(samples.Skip(1),
    (s0, s1) =>
        new
        {
            Sample = s0,
            Delta = s1.DateSample - s0.DateSample,
        })
    .Where(result => result.Delta.TotalMinutes >= 15)
    .Select(result => result.Sample);

answered Jun 28 '11 at 21:29

Rick Sladkey

33,988
6
71
95

Zip! i always forget/confuse that one. – John Gardner Jun 28 '11 at 21:33
Perfection! Excellent answer Rick. I always forget about Zip also. – KevDog Jun 29 '11 at 15:23

Magnus · Answer 2 · 2011-06-28T21:44:28.917

Assuming the list is already ordered.

var q = list.Select ((value, index) => new {value, index});
var valuesWithGaps = 
            from a in q
            join b in q on a.index + 1 equals b.index
            where (b.value.Date - a.value.Date).TotalMinutes > 15
            select new {a, b}; /* There is a gap greater than 15 min 
                                * between items a and b
                                */

score 1 · Answer 3 · answered Jun 28 '11 at 21:30

My Take: Haven't tested though:

        int i = 0;
        var res = from s1 in _data 
                  orderby s1.DateSampleTaken 
                  select new { row_num = i++, DateSampleTaken = s1.DateSampleTaken };
        var res2 = from s1 in res
                   join s2 in res on s1.row_num equals s2.row_num + 1
                   where (s2.DateSampleTaken - s1.DateSampleTaken) > 15
                   select s1.DateSampleTaken;

score 0 · Answer 4 · edited May 23 '17 at 11:52

0

This question shows how you can get a single gap, but I can't think of how to get a list of gaps without a loop of some kind...

Here is an article where you can get a generic method, that does this, but it uses loops.

Sorry I couldn't give you a cool LINQ query to do it, but at least other people stumbling across this question may get some answers in the above links.

edited May 23 '17 at 11:52

Community

1
1

answered Jun 28 '11 at 21:18

jrizzo

1,652
4
17
29

score 0 · Answer 5 · answered Jun 28 '11 at 21:21

I think you could use the Select((e, idx) => new { Date = e.DateSampleTaken, Idx = idx}) "indexed" overload to get a list of (date, idx) pairs from your sorted list. If you have that you could cross-join it with "itself" in a way that idx2=idx1+1 and select the difference of the dates. I see a good chance that this will work with LINQ 2 objects. However, I have some doubts that any O/RM framework can translate such tricks to SQL. Usually the expression power of LINQ is much higher than what any O/RM can translate to SQL, but usually this turns out only if you try. On the other hand you could probably solve this in SQL similarly, if your database server allows selecting the row index in SQL.

score 0 · Answer 6 · answered Jun 28 '11 at 21:22

0

you could maybe create a custom enumerator that iterated over any sequence and used a predicate to compare any object and the next object?

answered Jun 28 '11 at 21:22

John Gardner

24,225
5
58
76

How can I find all data with a given time span

6 Answers6