On Sat, 31 Aug 2013 22:04:54 +0200 Petite Abeille <[email protected]> wrote:
> > I changed the data (see below) because *meaning* of recordings.ends > > should be an "exclusive end", what's knows as a half-open interval. > > That makes the question of "does b follow a" one of equality. It is > > likely not the case that exactly one second transpires between two > > files. Rather, there is *no* time between them, implying that the > > end time is at the "end of that second". Much easier to think of > > both as being the beginning of the second. > > Meant to ask you about that? is it really worthwhile the overall > complication? That half-open interval business that is? > > After all, the idiomatic way to query point in time data would be > using 'between', no? Yes and yes. :-) I grant that using an exclusive end date runs afoul of BETWEEN. Nevertheless, exclusive end dates are your friend, because they give you JOIN joy in return. As a practical matter, exclusive end dates make self-joins easier, because otherwise to find a successor you have to know what the "missing" time is between the end of one and the start of the next. They're also more stable. In the OP's example, that missing time is 1 second or three minutes, or something. A minor upstream change in how the files are created could make that something else, changing the application's artifact but *not* changing the notion of two files being contiguous in time. Better to record the fact that they are contiguous (by making the end and start times the same) and relying on simple equality to express the idea. (The file's runtime can be recorded separately, if it matters.) Lots of time-series have that property. Take month-end dates, or stock prices, for instance. Here in the US, Monday is a holiday. For some purposes, the price of stocks traded on the exchanges won't change again until Tuesday morning. If you record them as ending on Friday, you have to know the exchange calendar to determine whether Tuesday is the successor or there's a gap. If you record them as ending on Tuesday, not only is the exchange calendar irrelevant, but you have the correct ends to compute the return. I need hardly point out that exclusive end dates are efficient, too. Using an exclusive end date, we join two dates on simple equality. No datetime arithmetic, just a bitwise comparison. Someone is bound to point out that SQLite will seize upon the index, too. The downside is confusion, especially if your database has both kinds. Naming conventions can help, but education matters more. Half-open intervals are unnatural at first. Like counting from zero, it's one of those things that gets easier with time and yields benefits. --jkl _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
