Re: FW: bitemporal functionality for PostgreSQL

On Sun, 2008-02-03 at 10:48 +0000, Heikki Linnakangas wrote:
> I don't know what "bitemporal" is all about, but to me, the
> pgsql-temporal approach is clearly the most flexible and attractive.

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.

> SELECT * FROM mytable1;
> timeatt
> -------
> 10 - 20
> 30 - 40
>
> SELECT * FROM mytable2,
> timeatt
> -------
> 15 - 35
>
> SELECT * FROM mytable1
> INTERSECT
> SELECT * FROM mytable2;
> timeatt
> -------
> 15 - 20
> 30 - 35
>
> as well as declaring uniqueness constraints, so that there's no rows
> with overlapping intervals, foreign key references on intervals etc.

I'd like to see those things as well.

I think it would be relatively straightforward to define some kind of
generalized join operator (of which INTERSECT can be a special case),
but optimizing it properly would be difficult. We could extend type
definitions to optionally include the operators necessary to perform the
generalized join. We could do similar things for other relational
operators.

I think for the key constraint (which is not the same as uniqueness),
we'd need to make a new index access method (or perhaps extend GiST
somehow).

If you have any ideas, I'd like to hear them. Obviously, my ideas are
still far away from a feature proposal.

> 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. It presents a very elegant solution to
> temporal issues, fully compatible with the relational model. Unlike
> "time travel" kind of approaches.

I second this statement. That's an excellent book, both analytical and
practical. I think my time interval type is fairly faithful to that
approach (although as I said, I really need to document it properly).

Regards,
Jeff Davis