Note that this will work for discrete data from a sensor but will not properly historize continuous data. That is, if what you are historizing is process data such as a temperature, this will not permit you to re-create the original engineering data. For that you need to allow the last duplicate to be stored and also store the computed slope to prior with each append -- in that case triggers would be the only way to do it.
-- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: Keith Medcalf <kmedc...@dessus.com> >Sent: Tuesday, 25 February, 2020 11:18 >To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org> >Subject: RE: [sqlite] Fwd: inserting new data only > > >create table data >( > key text primary key, > data integer not null >) >without rowid; > >-- insert into data select (?, ? as value where value IS NOT (select data >from (select max(key), data from data)); > >insert into data select '10:32', 12 as value where value IS NOT (select >data from (select max(key), data from data)); >insert into data select '10:35', 15 as value where value IS NOT (select >data from (select max(key), data from data)); >insert into data select '10:37', 15 as value where value IS NOT (select >data from (select max(key), data from data)); >insert into data select '10:39', 13 as value where value IS NOT (select >data from (select max(key), data from data)); >insert into data select '10:43', 13 as value where value IS NOT (select >data from (select max(key), data from data)); >insert into data select '10:46', 18 as value where value IS NOT (select >data from (select max(key), data from data)); > >select * from data; >10:32|12 >10:35|15 >10:39|13 >10:46|18 > >Constraints: > >(1) Will only work for appending data (new key > all keys in table) >(2) Types of key and data are immaterial as long as you are only >inserting (appending) new keys. > >-- >The fact that there's a Highway to Hell but only a Stairway to Heaven >says a lot about anticipated traffic volume. > >>-----Original Message----- >>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On >>Behalf Of Przemek Klosowski >>Sent: Tuesday, 25 February, 2020 10:02 >>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >>Subject: [sqlite] Fwd: inserting new data only >> >>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? >>_______________________________________________ >>sqlite-users mailing list >>sqlite-users@mailinglists.sqlite.org >>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
