The newest version of the Oracle Database (12c) has some interesting features. One of the more relevant features to me is temporal queries. More specifically, this is the ability to specify Transaction Time and Valid Time for each row, in accordance with the SQL 2011 ^{1 2} standard.

“Transaction Time” in this context would mean the period of time which the tuple (aka row) was marked as meaningful in the database, and “Valid Time” would mean the period of time for which the statement/fact being made in the tuple was true in the real world. In almost 100% of cases these are different things, and so you need different ways to track them.Also, when extending the SQL standard to incorporate these features we don’t want to make everyone have to totally redesign their databases to incorporate them, because the truth is that people won’t go back and do this — database models can persist for 30 or 40 years or more.

Thinking about representations of truth versus actual truth, perception, etc., makes me want to start thinking and talking about some 17th century philosophy but this probably isn’t the blog entry to do it in :).

In case you are curious, here is Oracle 12c compliance with all SQL 2011 Core features: http://docs.oracle.com/database/121/SQLRF/ap_standard_sql003.htm#SQLRF55516

I could not find a document from Oracle listing detailed compliance with SQL 2011 Optional features (which temporal support is one of.) I will say through my experiments that I have found that Oracle does provide limited support in some situations, but it is still questionable for me whether I would truly choose to roll this out in a Production environment.

I am working on some concrete examples of temporal columns and particular situations where they might be useful and will post them soon.

1. General discussion here of temporal validity in the SQL 2011 standard: http://www.sigmod.org/publications/sigmod-record/1209/pdfs/07.industry.kulkarni.pdf
2. An interesting summary here of other relevant changes in the SQL 2011 standard: http://www.sigmod.org/publications/sigmod-record/1203/pdfs/10.industry.zemke.pdf.