SQLite query rows nearest to hour

sqlite

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.

Best Answer

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

Related Solutions

SQLite writing a query where you select only rows nearest to the hour

Assuming that you want a SQL-only solution and your table is defined as:

CREATE TABLE measurements (
  pk INTEGER PRIMARY KEY,
  timestamp INTEGER NOT NULL,
  value TEXT NOT NULL,
  sensor_id INTEGER);

then create the following views:

CREATE VIEW vw_measurements AS
SELECT pk, timestamp, value, sensor_id,
       3600*ROUND((timestamp+1800)/3600) AS hour,
       ABS(timestamp - 3600*ROUND((timestamp+1800)/3600)) AS distance
FROM measurements;
CREATE VIEW min_distances AS
SELECT hour, MIN(distance) AS min_distance
FROM vw_measurements
GROUP BY hour;

Then you can get the records closest to every hour as:

SELECT m.hour, GROUP_CONCAT(m.timestamp), GROUP_CONCAT(m.value)
FROM vw_measurements m, min_distances d
WHERE m.hour = d.hour AND m.distance = d.min_distance
GROUP BY m.hour;

WARNING: SQLite SQL lacks many useful constructs that other SQL variants have, so the solution above can't discriminate between records with timestamps equidistant to their closest hour (either same timestamps or hour±samedelta); ergo the GROUP_CONCAT aggregate. Typically each row will have a single timestamp and value.

Note that if you have many thousands of records, the performance can be abysmal (tested on SQLite 3.7.9), and it might be better if min_distances becomes a temporary table.

Also: this solution does not fetch NULL values for hours without any related records (plus/minus 30 minutes).

Sqlite – Query on table

As I understand it sqlite does not have window functions which really simplify these kind of queries (I replaced a couple of identifiers which I suspect is reserved words):

select dt, tm, lat, lng
from (
    select dt, tm, lat, lng
         , row_number() over (partition by dt, lat, lng 
                              order by tm asc) rn1
         , row_number() over (partition by dt, lat, lng 
                              order by tm desc) rn2
    from ReportAct_tbl
) x
where 1 in (x.rn1, x.rn2)

Without window function (like row_number) it's probably easiste to get the row where its does not exists any rows that date before that time

select dt, tm, lat, lng
from ReportAct_tbl r1
where not exists (
    select 1 
    from ReportAct_tbl r2
    where r1.dt = r2.dt
      and r1.lat = r2.lat
      and r1.lng = r2.lng
      and r1.tm < r2.tm
)

To get the last rows we do the same:

select dt, tm, lat, lng
from ReportAct_tbl r1
where not exists (
    select 1 
    from ReportAct_tbl r2
    where r1.dt = r2.dt
      and r1.lat = r2.lat
      and r1.lng = r2.lng
      and r1.tm > r2.tm
)

and finally we can combine them as:

select dt, tm, lat, lng
from ReportAct_tbl r1
where not exists (
    select 1 
    from ReportAct_tbl r2
    where r1.dt = r2.dt
      and r1.lat = r2.lat
      and r1.lng = r2.lng
      and r1.tm < r2.tm
)
union
select dt, tm, lat, lng
from ReportAct_tbl r1
where not exists (
    select 1 
    from ReportAct_tbl r2
    where r1.dt = r2.dt
      and r1.lat = r2.lat
      and r1.lng = r2.lng
      and r1.tm > r2.tm
)

Best Answer

Related Solutions

SQLite writing a query where you select only rows nearest to the hour

Sqlite – Query on table

Related Question