CODEWITHSUNDEEP
sqlsql-serversql-server-2012
pecito22
pecito22

Reputation: 155

SQL Server : select rows from each seconds

I have a table where are inserted information about GPS points on every 5 or 3 seconds

For example:

id  timestamp
1   2016-02-06 19:33:02  
2   2016-02-07 19:33:07  
3   2016-02-08 19:33:10  
4   2016-02-09 19:33:15  
5   2016-02-10 19:33:20  
6   2016-02-11 19:33:23  
7   2016-02-12 19:33:27  
8   2016-02-13 19:33:32  
9   2016-02-14 19:33:37  
10  2016-02-15 19:33:40  
11  2016-02-16 19:33:45  
12  2016-02-17 19:33:48  
13  2016-02-18 19:33:53  
14  2016-02-19 19:33:56  
15  2016-02-19 19:34:01  
16  2016-02-19 19:34:05  
17  2016-02-19 19:34:08  
18  2016-02-19 19:34:11  
19  2016-02-19 19:34:16  
20  2016-02-19 19:34:21  
21  2016-02-19 19:34:24  
22  2016-02-19 19:34:29  
23  2016-02-19 19:34:34  
24  2016-02-19 19:34:37

I want to get information from every 30 seconds.

The result should be like this: 

id  timestamp
1   2016-02-06 19:33:02  
8   2016-02-13 19:33:32  
15  2016-02-19 19:34:01  
23  2016-02-19 19:34:34

Upvotes: 1

Views: 234

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use grouping to get the first id value of each 30-second interval:

SELECT t1.id, t1.timestamp
FROM mytable AS t1
INNER JOIN (
  SELECT MIN(id) AS id      
  FROM mytable
  GROUP BY CAST(timestamp AS DATE),
           DATEPART(hh, timestamp),
           DATEPART(mi, timestamp),         
           DATEPART(s, timestamp) / 30
) AS t2 ON t1.id = t2.id

Demo here

Upvotes: 3

Related Questions