SQL query to calculate time spans based upon time stamps

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.

Answers


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


I played with a simple table with int cols t1 and t2 and got the right results from this query, I think. Adapt the penultimate line as needed!

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
);

It's a triple-nested select. The outer one sums all the differences found. and is just the first and last lines. The second level - from the 2nd line - does most of the work. The inner select delivers a list of table row and timestamp values for the second level to play with.

The action is to sum the differences of all rows that have a "t1" that differs by 1 from the next higher row.

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

... forgot to say. t1 was supposed to be the timestamp. t2 was representing "other data".


Need Your Help

How to convert Unicode file to ASCII file in perl script on windows machine

windows perl unicode ascii

I have a file in Unicode format on a windows machine. Is there any way to convert it to ASCII format on a windows machine using perl script

Inserting Wordpress Plugin content to posts

wordpress wordpress-plugin wordpress-plugin-dev

I am trying to learn more about Wordpress and creating plugins. I have seen an existing plugin use a technique where you can add a 'reference' to it within your posts and WP will parse it and repla...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.