SQLite Default Timeout Value in Connection String – Explanation

Based on CL's suggestion to take another look at last_insert_rowid() and now that it's clear that INSTEAD OF triggers are only for VIEWs (see question's comments), here's a working version:

-- We need a view to create the trigger on
CREATE VIEW Container_Auto AS select * from Container;

-- Since it's automatic we need to check if we need to create a root
CREATE TRIGGER Rooter_Auto
INSTEAD OF INSERT ON Container_Auto WHEN (new.root IS NULL) BEGIN
    INSERT INTO Item(name, parent) VALUES ('ROOT', NULL);
    INSERT INTO Container VALUES (new._id, new.name, last_insert_rowid());
END;
-- If we don't need a root (i.e. the client supplied it), just pass the values through
CREATE TRIGGER Rooter_Transparent
INSTEAD OF INSERT ON Container_Auto WHEN (new.root IS NOT NULL) BEGIN
    INSERT INTO Container VALUES (new._id, new.name, new.root);
END;

-- Tests
INSERT INTO Item VALUES (1, 'ROOT', NULL);
INSERT INTO Container VALUES (1, 'My Box', 1);
INSERT OR IGNORE INTO Container(name) VALUES('Won''t work');
INSERT INTO Container_Auto(name) VALUES('All Automatic');
INSERT INTO Container_Auto(_id, name) VALUES(100, 'Specific ID');
INSERT INTO Container_Auto(name, root) VALUES('Specific root', 1);

-- Results
SELECT '---- Container ----';
SELECT * from Container;
SELECT '---- Item ----';
SELECT * from Item;

Output with comments:

---- Container ----
1|My Box|1          -- everything as we specified
                    -- note the OR IGNORE failed because root IS NULL
3|All Automatic|2   -- the failed insert incremented the rowid as well as created a new root
100|Specific ID|3   -- the supplied _id is used, new root is created
101|Specific root|1 -- the supplied root is used, _id is incremented
---- Item ----
1|ROOT|             -- created manually
2|ROOT|             -- created automatically by INTO Container_Auto(name)
3|ROOT|             -- created automatically by INTO Container_Auto(_id, name)

Notes:

• Using INSERT INTO Table VALUES format helps to catch schema modification bugs since when a new column is added the INSERT will fail instead of ignoring the new column.
• One can still use the Container table to insert into.
• last_insert_rowid() doesn't work when used after an INSERT INTO on a view with INSTEAD OF INSERT, i.e. after INSERT INTO Container_Auto, you'll have to requery the inserted row :(

Your second approach is not normalized: adding another sensor would require creating a new table, and querying multiple sensors would be horribly complex.

When all four sensors get a new value every second, and if you have multiple tables, then the database would have to update four tables. A single table is certainly more efficient.

For fast queries on time ranges, you need an index on the timestamp column. This implies that every INSERT also needs to update the index.
(If you want fast queries on time ranges for a single sensor, you need another index that contains both the sensor_id and timestamp columns.)

Please note that you do not need AUTOINCREMENT when you never delete rows. (Autoincrement In SQLite)