Get Past data from Sqlite3 database

Question

Hi

Suppose I insert a data point into a sqlite3 database on a nightly basis. Could anyone tell me how to obtain the data points for the past 30 days?

Thanks, Raj

Answer 1

SQLite is a very lightweight DB engine, so as far as I know, it doesn't store historical insert information. You have to have a date field in your table that gets set when you insert data.

One possible hack around this (if you're in a tight spot) is to use the auto-increment key/id field of your table to get the last 30 rows. This will only work if you haven't deleted any data since then, and have only been inserting exactly one row per day:

SELECT * FROM table WHERE id > ((SELECT MAX(id) FROM table) - 30)

Answer 2

Ensure the table has the timestamp of each data point.

CREATE TABLE smodgen (
    id INTEGER NOT NULL,
    when DATETIME NOT NULL,
    sensor_value INTEGER NOT NULL,
    PRIMARY KEY (id));

Then you can use that timestamp to query the data points.

SELECT id, when, sensor_value
FROM smodgen
WHERE
    when >= DATETIME('now', '-30 days');