On 12/1/18 8:24 AM, Joe Conway wrote: > Perhaps overkill, but if you represent your timeline as actual line > segments, perhaps PostGIS would be useful. E.g.: > > https://postgis.net/docs/manual-2.5/ST_Difference.html
That's an interesting notion. Thanks, Joe!
I think I learned enough about plpgsql programming last night to write the three basic functions that each operate on two time intervals:
1. determine if two intervals overlap 2. merge two overlapping intervals into one interval 3. given two overlapping intervals, produce the difference interval or interval set
My thinking here is that since these functions seem to be computationally simple, it might be more convenient for exploration, development, distribution and maintenance if there is some uniformity in their style and control over their implementation and behavior.
Currently, I am thinking about how these functions might be used to solve the problem. Since I am not familiar with the capabilities of SQL, my tendency is to think in terms of a function that iterates over the data set multiple times and eventually converges to produce the solution set. I am concerned that this might be a very goofy way to solve the problem in a relational database.
If you use postgis then you get all the fundamental primitives and (critically) indexing that is virtually guaranteed to function well. I used postgis awhile back to solve a data problem that was based in abstract dimensions and it worked incredibly well. I'm not an expert in the kind of extension programming you're attempting but I'd be worried that it's easy to make a mistake.
Postgis is going to give you maybe more guardrails to keep your work within safe design boundaries..
