SQL query to calculate time intervals based on timestamps

Question

In my project, I need to calculate the time span. I am currently retrieving every TimeStamp that matches my query and stores them in a List<> . I then iterate over the list to see if there are intervals of 10 seconds or less, and then I add them together. Anything greater than 10 seconds is ignored. I am wondering if there is an SQL query that I can do that will do this for me? I did a few searches but found nothing. In fact, I would not want to store so much information in my memory if I do not need it. Here is the method I use to iterate through my List<> :

 private static TimeSpan TimeCalculations(IList<DateTime> timeStamps) { var interval = new TimeSpan(0, 0, 10); var totalTime = new TimeSpan(); for (var j = 0; j < timeStamps.Count - 1; j++) { if (timeStamps[j + 1].Subtract(timeStamps[j]) > interval) continue; var timeDifference = timeStamps[j + 1].Subtract(timeStamps[j]); totalTime = totalTime.Add(timeDifference); } return totalTime; } 

The data that is currently being retrieved can be from 10 to 400 thousand rows of data. Here is an example:

 2006-09-07 11:46:09 2006-09-07 11:46:19 - 10 seconds 2006-09-07 11:46:20 - 1 second 2006-09-07 11:46:36 2006-09-07 11:47:49 2006-09-07 11:47:53 - 4 seconds 2006-09-07 11:48:02 - 9 seconds 2006-09-07 11:48:15 2006-09-07 11:48:29 2006-09-07 11:48:34 - 5 seconds 2006-09-07 11:54:29 2006-09-07 11:54:39 - 10 seconds 2006-09-07 11:54:49 - 10 seconds 2006-09-07 11:54:59 - 10 seconds 

This will result in approximately 59 seconds. This is the result I'm looking for.

I am using a SQLite database.

EDIT

Looking at the answers, I can say that my question was not complete enough. My current request for TimeStamps is sufficient. I am looking for a request to add the difference between them if the interval is 10 seconds or less.

Answer 1

Used your sample data to create sqlfiddle , and this query works against your sample data:

 SELECT DISTINCT tbl.timestamp FROM main_tbl tbl INNER JOIN ( SELECT temp.ID, temp.timestamp FROM main_tbl temp )test ON tbl.timestamp <= datetime(test.timestamp, '+10 seconds') AND tbl.timestamp >= datetime(test.timestamp, '-10 seconds') AND tbl.ID <> test.ID ORDER BY tbl.timestamp 

http://sqlfiddle.com/#!7/049f5/3

EDIT 2:

 SELECT sum( strftime('%s', ( SELECT min(temp.timestamp) FROM main_tbl temp WHERE temp.timestamp > tbl.timestamp ) ) - strftime('%s',tbl.timestamp) ) as total_sum FROM main_tbl tbl WHERE ( strftime('%s', ( SELECT min(temp.timestamp) FROM main_tbl temp WHERE temp.timestamp > tbl.timestamp ) ) - strftime('%s',tbl.timestamp) ) <= 10 AND date = "2013-05-13" AND col1 = col2 

http://sqlfiddle.com/#!7/049f5/55

Answer 2

I played with a simple table with int cols t1 and t2 and got the correct results from this query, I think. If necessary, adapt the penultimate line!

 SELECT sum(diff) FROM ( SELECT t_1.rowid AS this_id, other_t1 - t_1.t1 as diff FROM temp AS t_1 JOIN (SELECT t_2.t1 AS other_t1, t_2.rowid AS other_id FROM temp t_2 ) ON this_id = other_id-1 WHERE other_t1 - t_1.t1 = 1 ); 

This is a triple nested choice. External summarizes all the differences found. and this is only the first and last line. The second level - from the second line - does most of the work. The internal selection provides a list of table rows and timestamp values ​​for the second level for playback.

The action is to summarize the differences of all lines that have "t1", which differs by 1 from the next higher line.

To see the differences themselves, omit the first and last lines and replace ";"

... I forgot to say. t1 should have been a timestamp. t2 represented "other data".