Unique every N seconds. How?
(1) By anonymous on 2020-09-09 20:33:30 [link]
I want to define a table where identical entries (a combination of columns) within so many seconds apart are considered the same. If, for example, I try: `unique (col1,col2,dt between dt and datetime(dt,'+10 seconds'))` (where `dt` is a date/time field) I get an error: `expressions prohibited in PRIMARY KEY and UNIQUE constraints` Fine. Is there some other way to achieve this?
(2) By Richard Damon (RichardDamon) on 2020-09-09 20:42:39 in reply to 1 [link]
You could make a 'time' column that is only accurate to N seconds, so you could make a unique constraint base on it. You might also be able to set up a trigger to detect the condition and reject it
(3) By Keith Medcalf (kmedcalf) on 2020-09-09 21:10:47 in reply to 1 [link]
You cannot use expressions in the unique constraint inside a table definition, You may, however, use that expression in a CREATE UNIQUE INDEX staeement (the unique constraint in a table definition is merely syntactic sugar for a create unique index statement but it is restricted to pure column names and not expressions). That said, this will not be "unique every N seconds". You *might* achieve that by creating the following unique index: ``` qlite> create table t (col1, col2, dt); sqlite> create unique index ut on t (col1,col2,strftime('%s',dt)/10); ```
(4) By David Raymond (dvdraymond) on 2020-09-10 12:54:55 in reply to 3 [link]
I believe that would only slice things into predetermined 10 second windows. So if one entry was in the last second of its window, and the next entry was in the first second of the next window then it would let them in despite their being 1 or 2 seconds apart. I can't think of any way of doing this without having a trigger check it.
(5) By Ryan Smith (cuz) on 2020-09-10 13:34:43 in reply to 4
> I believe that would only slice things into predetermined 10 second windows I have to believe the OP meant this 10-second windowing precisely, well-aware of the fact that two consecutive writes might be milliseconds apart while falling in different 10-second-window-slices. Boundary-less groupings is not just hard to do in SQLite - It's physically impossible to do in this Universe within our current understanding of physics. Tangent: It is weird to be sure - It's such a normal human thing to imagine simply making little groups of everything that's really close together, be it in terms of colour, time, length, (any metric really). It's almost hard to intuitively know that this is impossible, and almost everyone who tried it in the past, we got burned. (Just take Biological species classification as an example, it's a lot more Rainbow and a lot less Lego than first imagined). Worse still, it is possible to define boundaries such that the groupings explicitly represents the "worst possible togetherness". Imagine for a moment a consistent timed event that fire exactly at 0.005 milliseconds of every second, and again at 0.995 of every second. If you group by "second" in which it occurs, you will have groupings where every event group has two entries that are 0.99 seconds apart, while in fact if you group every last-event in a second with the first-event in the next second, you will have much better groupings only 0.01 seconds apart. If your events/incidence were consistent in this way, you can preemptively design a "good grouping boundary", but most actual metrics will have a more random dispersion and any assumed group-boundary will probably be worse than good. There's some fun math to figure out best clumping boundaries on a set, but it's almost always irregular, and always dependent on the specific set and won't be useful in determining a standard for x unknown future sets. /tangent
(6) By anonymous on 2020-09-10 15:11:07 in reply to 5 [link]
Actually, I (OP) meant that a new entry should not be accepted unless it is at least 10 (N) seconds later than the previous one. Not exactly N, at least N. So, it could be 10 hours later but not less than N seconds. I quickly realized it is impossible to do this with a simple `UNIQUE` constraint. It would require `SELECT`ing the max dt for the given `COL1` and `COL2` combination, adding N seconds and comparing current dt value to be higher than that. If triggers can do arbitrary `SELECT`s outside of the current row, maybe something like this would work. I haven't tried yet but it is syntactically accepted by SQLite3. ``` CREATE TRIGGER log_trig BEFORE INSERT ON LOG BEGIN SELECT IIF(new.dt >= DATETIME((SELECT max(dt) FROM LOG a WHERE a.col1 = new.col1 AND a.col2 = new.col2), '+10 seconds'), new.dt, RAISE(ABORT,'Log too soon')); END; ```
(7) By David Raymond (dvdraymond) on 2020-09-10 15:30:08 in reply to 6 [link]
Try something like this ``` CREATE TRIGGER log_trig BEFORE INSERT ON LOG WHEN exists( SELECT 1 FROM LOG WHERE col1 = new.col1 AND col2 = new.col2 AND dt >= datetime(new.dt, '-10 seconds') ) BEGIN raise(abort, 'Log too soon'); END; ``` An index on col1, col2, dt should work with that just fine... I think.
(8) By anonymous on 2020-09-10 17:49:38 in reply to 7 [link]
It should be `SELECT RAISE(...` and it seems to work. `WHEN EXISTS` is more efficient than my way, I suppose. Still, I don't see why mine fails to work. Anyway, problem solved! Thanks.