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

Reply via email to