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

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.

CodePudding user response：

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

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