An Intellyx Brain Blog for JUXT by Eric Newcomer
Handling the element of time in SQL has been a challenge since it was created.
It’s difficult to address the challenge of temporality because SQL is designed and engineered for use with current databases, that is, databases designed for a query to return the current value of a data item following an INSERT or an UPDATE operation.
Current databases are designed to reflect the current state of the business (or organization). A query on a current database returns the current value of an inventory count for a particular item in stock, for example, not all prior values.
Because historical values are not preserved in current databases, it’s difficult to determine any value of a data item other than its current value (see also the earlier blog post in this series – Why it’s time to retire CRUD).
Richard Snodgress, who worked for years to resolve the temporality problem, describes the challenge in great detail in his book.
In short, current databases have to reason about time based on the current values of data items, rather than being able to query historical values, as temporal databases can – especially a bitemporal database.
Current databases retrieve the current values of time and date elements and compare them to derive a data item’s history, which requires a lot of work to create the right schema and to analyze query results.
A bitemporal database does that work for you.