Re: FW: bitemporal functionality for PostgreSQL

On Sun, 2008-02-03 at 15:56 -0500, Greg Smith wrote:
> 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.
>
> 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.

Ah, so "bitemporal" means both valid and transactional time stored in
the relation. I believe the definition of "fully temporal" I was
referring to was that it used intervals rather than time points.

It's interesting that you're using time points to represent that data. I
would think time intervals would be much more useful.

> 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.

Although I haven't read the whole Snodgrass book, out of what I did read
it seemed much less helpful. They mix a lot of the analysis of time data
management with SQL idiosyncrasies (and specific implementations), which
is not nearly so useful in something like PostgreSQL where we can make
our own data types.

Also, they use "period" to mean interval, and "interval" to mean
duration -- which is wrong, in my opinion; interval already has a well-
defined mathematical meaning.

Regards,
Jeff Davis