Re: FW: bitemporal functionality for PostgreSQL

On Sun, 3 Feb 2008, Jeff Davis wrote:

> I think what he means by "bitemporal" is what CJ Date, et al., refer to
> as "fully temporal" (as opposed to semi-temporal), that is, dealing with
> time intervals rather than time points.

I think fully temporal is a step of complexity above how some people use
bitemporal but this terminology is slippery.

Bitemporal databases are ones where every transaction gets two timestamps:
one at transaction commit and a second that encodes what time that
transaction is valid as of. A classic example uses a bitemporal employee
promotion table to track the company org chart. With the "valid as of"
timestamp in there, if you structure the query right you can generate a
report as of any particular point in time and find out what the structure
of the organization was at that point. There's all sorts of applications
where being able to track the state not just of the current table but of
where it was at a previous/future time can be handy, and bitemporal
approaches can be easier to handle than something like a history table.

While there's useful syntax to add, I believe one challenge of bitemporal
databases from the -hackers perspective involves how to accelerate the
typical queries people run. Here's some research that looks into things
like how to index the data usefully, and that gives more references into
the bitemp literature: http://www.cs.ucr.edu/~tsotras/temporal.html

>> I would suggest a book called "Temporal Data and the Relational Model",
>> by C. J. Date, Hugh Darwen and Nikos A Lorentzos to anyone who's
>> interested in temporal issues.

I think you need to be familiar with the work set down in both that one
and the Snodgrass/Jensen "Developing Time-Oriented Database Applications
in SQL" before you can even start do anything that's actually new in this
area. Bitemporal tables show up early in that book (P44 of the PDF
http://www.cs.arizona.edu/people/rts/tdbbook.pdf ) The way they use them,
the valid and transaction times are both intervals rather than points,
which I think makes the implementation there look more like Date's
fully-temporal tables.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD