SQL query to count total amount of time

Question

Hello,

I have a column in a SQL table that is the times that measurements were made. As shown in Table1 below, the measurement times can be out of sequence, repeated, or irregularly sampled (they are generally but not always 100 milliseconds apart). When the time stamp is missing (like 2009-12-20 11:59:56.1 in the example below), that means we did not make a measurement. So, I need to know the total amount of time over which we made measurements. That could be approximated as the count of unique _TimeStamp values multiplied by the typical time stamp (100 milliseconds). How do I write this query? It could have to run over a lot of records (30 days sampled at ~100 ms), if that affects the solution. Thanks!

Table1:
_TimeStamp
2009-12-20 11:59:56.0
2009-12-20 11:59:56.5
2009-12-20 11:59:56.3
2009-12-20 11:59:56.4
2009-12-20 11:59:56.4
2009-12-20 11:59:56.9 

Answer 1

How about

Select 100 * count(*)
From Table
Where _Timestamp between @startTime and @EndTime

Answer 2

Following on from Mark Byers' comment, in TSQL something like the following query would give you the difference between the min and max timestamps in milliseconds (varying the first parameter of DATEDIFF would allow you to change the unit the total amount time is reported in):

 SELECT DATEDIFF(millisecond, min(_TimeStamp), max(_TimeStamp)) as TotalTime
 FROM Table1

DATEDIFF reference

Answer 3

The DATEDIFF approach probably wouldn't work if you are concerned with the missing _TimeStamps. The straight count(*) would double count the duplicates (2009-12-20 11:59:56.4 in example).

So to exactly answer the question:

select count(distinct _timestamp) from table

This counts the number of distinct _Timestamp's in the table. You could then limit it to a specific _Timestamp range and multiply it out by an average sample time to get the final result.

Not sure what performance would be like but an index on the _Timestamp column would probably help.