Forum OpenACS Q&A: Databases with native bi-temporal support?
Does any such thing exist? Is anyone out their working on temporal support for, say, PostgreSQL? Anyone know of anything interesting?
The only thing I really found on the net was BtPgsql, a Ruby bi-temporal emulation layer for PostgreSQL.
There are or were some proposed standards for this temporal stuff, TSQL2 and SQL3/Temporal, but their current status sounds rather confused. Note that last link says in part, "Due to disagreements within the ISO committee as to where temporal support in SQL should go, the project responsible for temporal support was canceled near the end of 2001. Hence, the working draft, "Part 7, SQL/Temporal" is in limbo."!
Around that same time (June 2003), I also asked Rick Snodgrass about this and got back some useful info from him:
I think the list of resources in your message is quite complete. I don't know of any other efforts, except for our effort in the TAU project (see cs.arizona.edu/tau) to add transaction-time support to the internals of BerkeleyDB and MySQL. We're going for a production-quality system. Our alpha system works well, but is not of sufficiently high performance, so we're hard at work on making things run even faster. We plan on releasing the system as open source by the end of the year.Note that they're reusing their transaction-time enabled BerkeleyDB as the MySQL storage manager, which is probably the basic reason they're using MySQL at all.
There are no plans right now to support valid time. The support generally occurs in different modules. For MySQL/BerkeleyDB, the bulk of the tt support is in BDB. For valid time, the bulk would be in MySQL. If our tBDB/tMySQL takes off, then we'll consider adding vt support.
Yes, that's how we do it. Not that much has changed in MySQL, though we are now considering ways to take a predicate in transaction time in the WHERE clause and get it to BDB.
On the confused state of the Temporal SQL standards mentioned above, he said:
That is still the state. Basically the US and UK delegations couldn't agree, so the ISO committee gave up. I still have hopes that a DBMS vendor will decide that temporal support is sufficiently important to support natively; the draft standards provide a good design to implement.So, in case anyone else is ever interested in this temporal database stuff, that's everything I learned!