Proposal - temporal contrib module

[Scott Bailey <artacus(at)comcast(dot)net>]

I would like to add a temporal contrib module. The most important piece 
would be adding a period data type and some support functions. Jeff 
Davis and I both have temporal projects on pgFoundry, and we've been 
collaborating for a while. But there are some areas we'd like to get 
some advice on.

Disk format - A period can be represented as [closed-closed], 
(open-open), [closed-open) or (open-closed] intervals. Right now we 
convert these to the most common form, closed-open and store as two 
timestamptz's.

Nulls - A common use case for periods is for modeling valid time. Often 
the end point is not known.  For instance, you know when an employee has 
been hired but the termination time typically wouldn't be known ahead of 
time. We can either represent these with a null end time or with 
infinity. But I'm not sure how to deal with them. Obviously we can test 
for containment and overlap. But what about length or set operations?

Non-contiguous Sets - A period defines a contiguous set of time. But 
many times we need to work with non-contiguous sets (work shifts in a 
week, bus schedules, etc).  Right now, I'm using period arrays. But 
period arrays can contain overlapping and adjacent periods. And we have 
no way to indicate that a period array has been coalesced into a 
non-contiguous set. And what indexing strategies could be used with 
non-contiguous sets?

Temporal Keys - We need two types of temporal keys. A primary key, 
exclusion type prevents overlap so someone isn't at two places at the 
same time. And a foreign key, inclusion type so we can check that the 
valid time of a child is contained with in the valid time of the parent. 
Jeff is working on the former, but there is no easy way to do the latter.


There is actually a lot of theory out there but very few 
implementations. Although not an official standard, we try to follow the 
TSQL2 spec pretty closely. Further reading:

Developing Time-Oriented Database Applications - Snodgrass
http://www.cs.arizona.edu/~rts/tdbbook.pdf

TSQL2 spec ftp://ftp.cs.arizona.edu/tsql/tsql2/spec.pdf

Temporal Data and the Relational Model - Date et al
http://books.google.com/books?isbn=1558608559

Dozens of publications
http://timecenter.cs.aau.dk/pub.htm


Regards,

Scott Bailey