3 ms·

My personal opinion is this is difficult to do correctly and ergonomically in SQL. One has to be clear on segregating the metamodel (the machinery that supports

My personal opinion is this is difficult to do correctly and ergonomically in SQL. One has to be clear on segregating the metamodel (the machinery that supports as-of timekeeping) from the model (the actual data). What one winds up wanting is a different sublanguage, an additional as-of syntax on a per table basis, which kind of suggests that is not really the right solution domain. They are not SQL but Datomic[1] and Crux[2] are first class bitemporal databases, look there for design guidelines and commentary. 1. https://datomic.com/ https://datomic.com/ 2. https://opencrux.com/ https://opencrux.com/

j-pb 7y ago
Isn't datomic 'just' a temporal DB? Through their transactor design there's always a total order on events which removes the need for the whole "what did you see when you created this" vs. "what would you have seen if you would have had access to the entire distributed state of the system" shenanigans.
- jonahbenton 7y ago
  It is both. The transactor design ensures serialization level isolation for writes- but semantically writes are considered "creating facts" and the time at which they are written is the time at which they are "known" and one can ask questions about the state of the world- the collection of facts- that were known as of a particular time. And of course time is also a data type, so one can time based questions on multiple levels. The model is deeply general- facts are assertions that "this attribute of this entity had this value at this time." One neat capability is that you can "go back in time" and simulate applying transactions that did not occur, supporting counterfactual and hypothetical analytics, and various kinds of testing scenarios. Attempting to carry through the implications of that model at the application level in SQL is very hard.
  - j-pb 7y ago
    What you describe isn't a bitemporal database though, it's a temporal one. Bitemporality afaik means that you get two views on your data, one that describes state relative to individual writers (what you saw at write time in a distributed and potentially inconsistent system) and what is actually true considering global state (what if you had had absolute knowledge of the distributed state at write time).

j-pb 7y ago

Isn't datomic 'just' a temporal DB? Through their transactor design there's always a total order on events which removes the need for the whole "what did you see when you created this" vs. "what would you have seen if you would have had access to the entire distributed state of the system" shenanigans.

jonahbenton 7y ago
It is both. The transactor design ensures serialization level isolation for writes- but semantically writes are considered "creating facts" and the time at which they are written is the time at which they are "known" and one can ask questions about the state of the world- the collection of facts- that were known as of a particular time. And of course time is also a data type, so one can time based questions on multiple levels. The model is deeply general- facts are assertions that "this attribute of this entity had this value at this time." One neat capability is that you can "go back in time" and simulate applying transactions that did not occur, supporting counterfactual and hypothetical analytics, and various kinds of testing scenarios. Attempting to carry through the implications of that model at the application level in SQL is very hard.
- j-pb 7y ago
  What you describe isn't a bitemporal database though, it's a temporal one. Bitemporality afaik means that you get two views on your data, one that describes state relative to individual writers (what you saw at write time in a distributed and potentially inconsistent system) and what is actually true considering global state (what if you had had absolute knowledge of the distributed state at write time).