Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've got a set of data where data has been taken approximately every minute for about three month 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'm asking a lot, but this would be incredibly helpful

BTy the way, the table is just PK(autoincrement), timestamp, value, sensor id(FK). I've 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'), 
       value 
from  Timestream 
where idSensor = 359 ;
share|improve this question

2 Answers 2

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
WHERE ...

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

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

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)),
       time,
       value
FROM Timestream
WHERE ...
GROUP BY round(time / 3600)  -- or strftime(%H)
share|improve this answer

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

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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