How to build a daily history from a set of rows in SQL?

datetime, sql, sqlite

Solution

Let me define "closest value" as the first value after each 10-minute interval begins. You can generalize the idea to other definitions, but I think this is easiest to explain the idea.

Convert the timestamp to a string value of the form "yyyy-mm-dd hhMM". This string is 15 characters long. The 10-minute interval would be the first 14 characters. So, using this as an aggregation key, calculate the `min(id)` and use this to join back to the original data.

Here is the idea:

select isum.*
from indexes i join
     (select substr(strftime('%Y-%m-%d %H%M', timestamp), 1, 14) as yyyymmddhhm,
             min(id) as whichid
      from indexes
      group by substr(strftime('%Y-%m-%d %H%M', timestamp), 1, 14)
     ) isum
     on i.id = isum.whichid

Problem

I have a simple SQLite table that records energy consumption all day long. It looks like that: ``` rowid amrid timestamp value ---------- ---------- ---------- ---------- 1 1 1372434068 5720 2 2 1372434075 0 3 3 1372434075 90 4 1 1372434078 5800 5 2 1372434085 0 6 3 1372434085 95 ``` I would like to build a simplified history of the consumption of the last day by getting the closest value for every 10 minutes to build a CSV which would look like: ``` date value ---------------- ---------- 2013-07-01 00:00 90 2013-07-01 00:10 100 2013-07-01 00:20 145 ``` As for now I have a request that allows me to get the closest value for one timestamp: ``` SELECT * FROM indexes WHERE amrid=3 ORDER BY ABS(timestamp - strftime('%s','2013-07-01 00:20:00')) LIMIT 1; ``` How can I build a request that would do the trick to get it for the whole day? Thanks,

Original source