Search code examples
androidmysqlandroid-room

Android ROOM, get the List of rows in 5min interval over last 24h

I want to get List of rows in interval of 5 minutes for last 24h. I do store data each second in the database, but for the UI, I want to keep them in intervals.

timestamp         data1          data2
1670614969493     300            277.0
1670614970535     1783           277.0
1670614971553     809            277.0
1670615575889     66             275.0
1670615576934     930            275.0
1670615577953     141            275.0
1670616803058     348            250.0
1670616804080     361            250.0
1670616805107     356            250.0

For timestamp I'm using unix time so I can know the exact time of saving. Now, I want to be based on a clock 5 min interval instead of an 5min interval from the last save.

So what I do need:

10:55
10:50
10:45
.
.
.

In case there is no entry, use the closest one

What I don't need:

10:57
10:52
10:47
.
.
.

So, query to list rows from last 24h in 5min intervals based on the clock, not last entry timestamp.

Solution

  • I want to get List of rows in interval of 5 minutes for last 24h.

    First the times appears to include the milliseconds so for a unix time the value needs to be divided by 1000 to drop the milliseconds.

    To get 5 minute intervals then you need to GROUP BY the timestamp divided by 1000 to loose the milliseconds, then by 60 / 5 to get the 5 minute intervals. To include all the rows of an interval you then use the GROUP BY clause. Which could be:-

    GROUP BY timestamp  / (1000 * (60 * 5))

    To extract only the rows in the last 24 hours then you could use a WHERE clause that compares the timestamp BETWEEN now less 24 hours and now e.g.

    WHERE strftime('%s',timestamp / 1000,'unixepoch') BETWEEN strftime('%s','now','-24 hours') AND strftime('%s','now')

    You would then be able to use the SQLite Aggregate Functions as needed. It should be noted that other values e.g. data1 would, for an output row, be an arbitrary value from one of the rows.

    Here's an example/demo that uses the data as per the question:-

    /* Just in case */
DROP TABLE IF EXISTS thetable;
/* Create the demo table */
CREATE TABLE IF NOT EXISTS thetable (timestamp INTEGER, data1 INTEGER, data2 INTEGER);
/* Load the demo data */
INSERT INTO thetable VALUES 
(1670614969493,     300,            277.0),
(1670614970535,     1783,           277.0),
(1670614971553,     809,            277.0),
(1670615575889,     66,             275.0),
(1670615576934,     930,            275.0),
(1670615577953,     141,            275.0),
(1670616803058,     348,            250.0),
(1670616804080,     361,            250.0),
(1670616805107,     356,            250.0)
;
/* demonstrate various aspects */
SELECT 
    datetime(timestamp / 1000,'unixepoch'), /* 1) timestamp to date representation */
    strftime('%s','now','-24 hours') AS selection_from, /* 2) time now less 24 hours */
    strftime('%s','now') AS selection_until, /* 3) time now */
    count(*) AS rows_in_the_interval, /* 4) count aggregate function */
    sum(data1) AS data1_total, /* 5) sum aggregate function */
    avg(data2) AS data2_average, /* 6) avg aggregate function */
    * /* 7) ambiguity of using existing column for the group */
FROM thetable
/* 8) the where clause for the last 24 hours */
WHERE strftime('%s',timestamp / 1000,'unixepoch') BETWEEN strftime('%s','now','-24 hours') AND strftime('%s','now')
/* 9) group by 5 minute intervals */
GROUP BY timestamp  / (1000 * (60 * 5))
; 
/* Cleanup demo environment */
DROP TABLE IF EXISTS thetable;

    This results in:-

    enter image description here

    • The latter columns will be ambiguous as the value extracted will be one of the values from one of the rows.