←back to thread

Poor man's bitemporal data system in SQLite and Clojure

(www.evalapply.org)
169 points adityaathalye | 2 comments | 03 Sep 25 17:47 UTC | HN request time: 0.001s | source
Show context
moomin ◴[03 Sep 25 18:29 UTC] No.45118981[source]▶
It's a pity that Clojure is kind of a hermetic space these days, because the concept of bitemporality really deserves much more attention. It's amazing how often you want to know "What was the P&L for March using the data available on Apr 4?" and how uncommon it is to find a database design that supports that kind of query.
replies(4): >>45120261 #>>45120290 #>>45120880 #>>45120882 #
necubi ◴[03 Sep 25 22:04 UTC] No.45120880[source]▶
It's not particularly rare in analytical databases/query engines, nearly all of which support AsOf joins these days, including

  * Clickhouse
  * DuckDB
  * BigQuery
  * Snowflake
  * QuestDB
replies(1): >>45121943 #
1. refset ◴[04 Sep 25 00:22 UTC] No.45121943[source]▶
AsOf join in those systems solves a rather narrow problem of performance and SQL expressiveness for data with overlapping user-defined timestamps. The bitemporal model solves much broader issues of versioning and consistent reporting whilst also reducing the need for many user-defined timestamp columns.

In a bitemporal database, every regular looking join over the current state of the world is secretly an AsOf join (across two dimensions of time), without constantly having to think about it when writing queries or extending the schema.

replies(1): >>45123286 #
2. sterlinm ◴[04 Sep 25 03:40 UTC] No.45123286[source]▶
This is the book I like for an overview of the problem. https://www.amazon.com/Temporal-Relational-Kaufmann-Manageme...