I’ve been interested in temporal data and relational databases for quite some time. There are going to be at least two people talking about temporal data at PostgreSQL WEST in Seattle: Scott Bailey and me. See the talk descriptions.
In the past, I’ve worked on a temporal extension to PostgreSQL that implements the PERIOD data type. This is a data type that offers both a definite beginning and a definite end time, which is important for describing things that happen over a period of time, rather than instantaneously. Trying to use separate attributes for “start” and “end” is bad for a number of reasons, and will certainly be addressed in a subsequent blog entry. For now, I’ll just say that I believe the PERIOD data type is fundamentally important for handling all kinds of time data, which I believe is a common problem.
At WEST, I’ll be presenting my progress on temporal keys. Temporal keys are used to prevent overlapping periods of time — a schedule conflict — by using an index and following the same concurrent behavior as UNIQUE with minimal performance cost (one extra index search, to be precise).
Temporal keys cannot be expressed in PostgreSQL 8.4, unless you resort to triggers and a full table lock (ouch!). So, additional backend support is required. This is accomplished in my patch for operator exclusion constraints, which are a more general way of using arbitrary operators and index searches to enforce a constraint. I plan to do what’s required for the patch to be accepted in PostgreSQL 8.5.
Temporal modeling is a common problem. It seems like almost every PostgreSQL conference has had at least one talk on the matter, so we know there is some demand for improvement. If you’re interested, I hope you come to WEST and chat with Scott or I, and let’s see if we can come up with some real solutions.
That sounds like a very useful development, and I can already see the reduction in clumsy and slow code for date-checking and event clashes. I can only make it to PGDay in Paris unfortunately, but I’ll be following news on this where I can.
It sounds like the alterations for your operator exclusion contraints could pave the way for more useful indexable features like this.
Nice work Jeff!
One of the reasons I like PostgreSQL so much is that it handles dates and time better than most other databases. Although a PERIOD data type is useful for representing *definite* start and end times, another related problem is representing *indefinite* start and end times. One use case (that I had experienced myself) is having to represent employee work-shifts, such as an *indefinite* Monday from 7:00am to 5:00pm (e.g. *any* Monday), and having to ask: does ’2009/10/05 12:00′ as a *definite* instant of time fall within that *indefinite* span of time? It’s a fairly common problem to ask, within which work-shift did this even occur? Unfortunately, PostgreSQL (or any other DBMS I tried) couldn’t help unless I converted the indefinite time to definite timestamps, so I used pgsql functions for this. The logic gets pretty hairy as you hit midnight, cross into the next year, have a work-shift that crosses multiple days, etc.
Yeah, that’s a tough one, I’m glad you brought that up. There are a few tricks, like using generate_series, that can make it almost bearable without resorting to procedural code.
I’ll spend some more time thinking about this one and I’ll see what other people have to say. I think that a reasonable solution exists if we make a significantly more complex type that represents am infinite set of non-contiguous periods. That would be a significant amount of work, and we’d need to make the internal representation flexible enough to represent many patterns.
Rob,
Actually you can solve this problem w/o any procedural code. You use the combination of a schedule table and a calendar table to project those into periods in a view. From there its easy to test for overlap or containment.
I’m planning on writing a couple articles on how to use periods and scheduling so I’ll be sure to cover this.
Scott
if you are interested i came across some articles on how to use periods and scheduling by http://www.sharedshares.com SE. hope you’ll find this information as interesting as I did.