On Tue, Feb 25, 2020 at 11:03 AM Przemek Klosowski < przemek.klosowski+sql...@gmail.com> wrote:
> I am storing time series data arriving from a sensor into (time,value) > records, like so: > 10:32 12 > 10:35 15 > 10:37 15 > 10:39 13 > 10:43 13 > 10:46 18 > > and I want to avoid storing repetitive data, so that the database should > contain > 10:32 12 > 10:35 15 > 10:39 13 > 10:46 18 > where only the earliest time with the unchanging value is stored. > > I don't see how INSERT could be conditional on e.g. value != (select > value from tbl order by time descending limit 1), so I thought I'd use > triggers. The only way I could think of was to delete the new > duplicate record after it has been inserted: > > create trigger cull after insert on tbl when > (select value-lead(value) over (order by time desc) from a limit 1) = 0 > begin > delete from a where time like new.time; > end; > > Is there a simpler way? > _______________________________________________ > Why not: CREATE TABLE ME (ATIME TIME, VALUE INTEGER PRIMARY KEY); You can't INSERT duplicate numbers into the "VALUE" column, it will fail. -- People in sleeping bags are the soft tacos of the bear world. Maranatha! <>< John McKown _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
