I've got a set of data where data has been taken approximately every minute for about three months and the time has been stored as a UNIX timestamp. There is no regularity to the timestamp (i.e. the zero minute of the hour may not contain a reading, 00:59:55 and the next measurement could be 01:01:01) and days may be missing.

What I need is the row nearest to the hour, with the time-step rounding to the hour, as long as the nearest value is not more than 30 minutes away from the hour.

Where a matching hour could not be found it would be helpful if the query could include a time but no value.

I realize I am asking a lot, but this would be incredibly helpful.

By the way, the table is just PK(autoincrement), timestamp, value, sensor id(FK). I have tried this to get the data out:

SELECT strftime('%S', time, 'unixepoch'), 
       strftime('%M', time, 'unixepoch'), 
       strftime('%H', time, 'unixepoch'), 
       strftime('%d', time, 'unixepoch'),      
       strftime('%m', time, 'unixepoch'), 
       strftime('%Y', time, 'unixepoch'), 
FROM Timestream 
WHERE idSensor = 359;
  • Do you still need an answer for this? Dec 12 '14 at 9:16

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

CREATE TABLE measurements (
  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).

  • As an example: a table with 29301 rows used about 10 minutes CPU time (on a 1.6 GHz Atom processor) to produce a result.
    – tzot
    Jun 1 '13 at 12:04

The unix epoch happens to be on the hour, so what you want to do is to round your timestamps to the next multiple of the number of seconds in one hour (3600). So,

  1. divide by 3600 to get the number of hours;
  2. round to whole numbers; and
  3. multiply by 3600 to get the seconds back:
SELECT round(time / 3600) * 3600 AS HourTimestamp
FROM Timestream

To find the actual timestamp nearest to that, compute the difference:

SELECT abs(time - round(time / 3600) * 3600) AS Difference
FROM Timestream

To find the nearest for each hour, get the record with the minimum difference for each hour (this requires SQLite 3.7.11 or later):

SELECT MIN(abs(time - round(time / 3600) * 3600)),
FROM Timestream
GROUP BY round(time / 3600)  -- or strftime(%H)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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