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

Question

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,

Answer 1

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