SQL Server : select rows from each seconds -


i have table inserted information 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 information every 30 seconds.

the result should 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

you can use grouping first id value of each 30-second interval:

select t1.id, t1.timestamp mytable t1 inner join (   select min(id) id         mytable   group cast(timestamp date),            datepart(hh, timestamp),            datepart(mi, timestamp),                     datepart(s, timestamp) / 30 ) t2 on t1.id = t2.id

demo here


Comments

Post a Comment

Popular posts from this blog

routing - AngularJS State management ->load multiple states in one page -

Image
i developing site in angularjs ui-router. have situation on button click need change , load 3 state page , 3 looks image .please me how can load multiple pages. in 1 page you need configure router config this: $stateprovider .state("state1", { url: "/url1", controller: "firstcontroller", views: { view1: { templateurl: "templates/template1.html" }, view2: { templateurl: "templates/template2.html" }, view3: { templateurl: "templates/template3.html" } } }).state("state2", { url: "/url2", controller: "secondcontroller", views: { view1: { templateurl: "templates/template4.html" }, view2: { templateurl: "templates/template5.html" }, view3: { templateurl: "templates/template6.html" } }).state("state3", { ...
Read more

python - GRASS parser() error -

i'm trying use grass on python 2.7, i'm having problems @ setting script on idle, i'm getting error @ parser() function: here script: import os import sys gisbase = os.environ['gisbase'] = 'c:\program files (x86)\grass gis 7.0.1rc1' gisrc = 'c:\grassdata' gisdbase = 'c:\grassdata' location = 'newlocation' mapset = 'tc' ld_library_path = 'c:\program files (x86)\grass gis 7.0.1rc1\lib' path = 'c:\program files (x86)\grass gis 7.0.1rc1\etc';'c:\program files (x86)\grass gis 7.0.1rc1\etc\python';'c:\program files (x86)\grass gis 7.0.1rc1\lib';'c:\program files (x86)\grass gis 7.0.1rc1\bin';'c:\python27';'c:\program files (x86)\grass gis 7.0.1rc1\python27';'c:\program files (x86)\grass gis 7.0.1rc1\msys' pythonlib = 'c:\python27' pythonpath = 'c:\program files (x86)\grass gis 7.0.1rc1\etc\python' sys.path.append(os.path.join(os.environ['...
Read more

Swift game error message -

i following ios game tutorial. cannot figure out wrong here. error on these 2 lines. let newangle = percent * 180 - 180 cannon.zrotation = cgfloat(newangle) * cgfloat(m_pi); 180.0 //error "use of unresolved identifier 'percent'" the tutorial uses: cannon.zrotation = cgfloat(newangle) * cgfloat(m_pi)/ 180.0 but changed error suggested import spritekit class gamescene: skscene { var cannon: skspritenode! var touchlocation:cgpoint = cgpointzero override func didmovetoview(view: skview) { /* setup scene here */ cannon = self.childnodewithname("cannon") as! skspritenode } override func touchesbegan(touches: set<uitouch>, withevent event: uievent?) { /* called when touch begins */ touchlocation = touches.first!.locationinnode(self) } override func touchesmoved(touches: set<uitouch>, withevent event: uievent?) { touchlocation = touches.first!.locationinn...
Read more