Forum OpenACS Q&A: Databases with native bi-temporal support?

A while back, some folks here pointed out an excellent book by Snodgrass, Developing Time-Oriented Database Applications in SQL. However, the central thing I took from that book is that no way do I ever want to try to do any serious, practical bi-temporal work in standard SQL-92. I'd want either a good translation layer to take nice temporal DDL and DML and convert it to standard SQL, or preferably, native temporal support in the RDBMS itself.

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

Back in June 2003 when I was looking into this, I also found Tiger, TimeDB, Espresso, and JTemporal, all of which look like emulation layers of sorts. TimeDB is clearly closed source, I'm not sure about Tiger. Espresso and JTemporal are open source. Tiger and TimeDB both appear to be inactive.

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.

[...]

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.

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.

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!