[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
Re: [sqlite] Fwd: inserting new data only
On Tue, Feb 25, 2020 at 12:22 PM David Raymond wrote: > > A before trigger which uses the raise function would stop it from getting > inserted in the first place. > > create trigger cull > before insert on tbl > when new.value = (select value from tbl order by time desc limit 1) > begin > select raise(ignore); > end; > > Or if you want it to actually return an error to let you know what happened > you could make it > select raise(abort, 'Repeated entry'); Ah, that's cool---I missed the 'ignore' possibility and thought that the before trigger can't prevent the subsequent insert. > > > -Original Message- > From: sqlite-users On Behalf > Of Przemek Klosowski > Sent: Tuesday, February 25, 2020 12:02 PM > To: SQLite mailing list > 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: inserting new data only
On Tue, Feb 25, 2020 at 1:03 PM John McKown 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. ... > Why not: > > CREATE TABLE ME (ATIME TIME, VALUE INTEGER PRIMARY KEY); > > You can't INSERT duplicate numbers into the "VALUE" column, it will fail. This won't work here because the same value COULD reappear later: 12 15 15 13 13 18 15 needs to be registered as 12 15 13 18 15 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: inserting new data only
On Tue, Feb 25, 2020 at 1:18 PM Keith Medcalf wrote: > create table data > ( > keytext 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)); >.. > 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. Awesome---exactly what's needed. The monotonicity of the time key variable is assured by how the data is collected---but is there a way to express that in sqlite? create table data ( key text primary key check (julianday(key) > julianday(select max(key) from data), data integer not null); ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: inserting new data only
On Wed, Feb 26, 2020 at 11:56 AM James K. Lowden wrote: > > and I want to avoid storing repetitive data, so that the database > > should contain > > [...] > > only the earliest time with the unchanging value is stored. > > Be careful what you wish for. Usually "avoid storing" is a proxy for > some kind of intended use. Unless it's infeasible, it's usually better > to store everything, verbatim, as it arrives. Then you can present it > however you like, with nothing lost. A lot of process measurement instrumentation returns some nominal values, for instance firmware version. Do we store it or not? Storing it every second is silly, but if we drop it, we wouldn't be able to for instance audit if the statistics changed in some subtle way because of a firmware bug. This 'store only changed values' is intended for situations like that. Another suitable candidate for that treatment might be a status, for instance 'on battery' value for a UPS monitoring system. I can't think of a scenario where storing it every time would be better. The only disadvantage I can think of would be incidental, like monitoring the monitoring system itself: a long period of 'no data' could be caused by a monitoring failure as well as by the value not changing; but if that is a concern, I think it would be better to store a single heartbeat rather than possibly multiple unchanging values. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
