SQL query to calculate time spans based upon time stamps

c#, sql, sqlite

Solution

Used your sample data to create a `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

Problem

In my project, I have need of calculating a time span. Currently, I'm retrieving every TimeStamp that matches my query and storing them in a `List<>`. Then, I iterate through the list to see if any intervals are 10 seconds or less, and then I add those together. Anything greater than 10 seconds is ignored. I'm wondering if there is a SQL query that I can do that will do this for me? I've done some searching, but didn't find anything. Essentially, I'd like to not have to store so much information in memory if I don't have to. Here's the method I'm using 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 being retrieved currently can be anywhere from 10 to 400k rows worth of data. Here is a sample: ``` 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 would result in about 59 seconds. This is the kind of result I'm looking for. The database I'm using is SQLite. EDIT Looking at the answers, I can tell that my question wasn't quite thorough enough. My current query to get the TimeStamps is sufficient. What I'm looking for is a query to add the difference between them together, if the interval is 10 seconds or less.

Original source