2

My question is similar to the one asked here. I'm new to SQL and I'm creating a weather app that stores part of the data locally in a db, so I'm writing the query using the sqlitedatabase java class for the android app.

I need to select a temperature row for each hour interval offset by 30 minutes (so 1:30-2:30) by taking the most recent to the hour reading. Here's what I've tried so far using a database of lat/lons that I have. I used avg() here because it may be easier to get a picture of the weather around that hour by averaging temperature reading within that interval. I've been using SQLite Studio to get a hang of writing queries in SQL:

select avg(longitude), time_stamp, strftime('%H',time_stamp) 
from events 
group by strftime('%H', time_stamp) + round((time_stamp - 1800)/3600)

And my result: 

-81.4919071241071   2015-11-11 11:21:53 11
-48.8079752772727   2015-11-11 12:59:31 12
-54.104869527907    2015-11-11 13:44:16 13
-88.0822953625      2015-11-11 14:37:38 14

It looks okay BUT there's data it's not including from 11:30-12:30, even though it's definitely present in the database. The first row is fine, the second row should go with 13, 3rd row with hour 14, and 4th row with hour 15.

Sample data with columns: row, event_id, type, machine_id, longitude, lat, time_stamp (datetime), other_machine, synched

42  1   2   -89.4813131 40.7400818  2015-11-11 11:27:30 3   0
43  9   1   -89.4813908 40.7401891  2015-11-11 11:21:53 -1  0
44  8   1             0          0  2015-11-11 11:27:52 -1  0
45  0   2   -89.4812174 40.7401841  2015-11-11 11:27:55 3   0
46  4   2   -89.4813087 40.7401857  2015-11-11 11:29:10 -1  0
47  5   2   -89.4813087 40.7401857  2015-11-11 11:29:13 -1  0
48  4   2   -89.4813087 40.7401857  2015-11-11 11:29:13 -1  0
49  5   2   -89.4812153 40.7401323  2015-11-11 11:30:02 -1  0
50  4   2   -89.4812153 40.7401323  2015-11-11 11:30:02 -1  0
51  2   2   -89.4812291 40.7400435  2015-11-11 11:30:20 3   0
52  5   2   -89.4812718 40.7401197  2015-11-11 11:30:46 -1  0
53  4   2   -89.4812718 40.7401197  2015-11-11 11:30:46 -1  0

This sample data shows where half of the points would group with hour 11 and the ones after 11:30 would go with 12.

Improve this question
2
  • Please include sample data and the results from you query so that those are both visible. What do the 'missing rows' in the result look like? Run you code against the sample data and show that as well.
    – RLF
    Commented Jan 8, 2016 at 21:23
  • @RLF I edited in some of the sample data I'm using for practice. In reality there are hundreds of data points. From the sample data the query should able to either 1) avg the first 7 rows and group those with hour 11 and the bottom rows would be grouped and averaged with the rest of the rows for 11:30 to 12:30. My query includes averages b/c i feel it may be more efficient than doing it the way it's done in the example i linked
    – M. Smith
    Commented Jan 8, 2016 at 21:40

1 Answer 1

Reset to default
3

time_stamp - 1800 subtracts two numbers. If the value in the time_stamp column is not a number, it is converted.

From what you've shown, converting the values in the time_stamp column (which begin with the characters 2015) to a number results in the value 2015; subtracting 1800 from that results in 215. This is probably not what you want.

To group by hours offset by half an hour, just add half an hour to the timestamp, then drop the minutes/seconds:

GROUP BY strftime('%H', time_stamp, '+30 minutes')
Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.