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

Reply via email to