
The Value of Bitemporality – Whose Time Is It Anyway? - tosh
https://juxt.pro/blog/posts/value-of-bitemporality.html
======
michelpp
There are a couple of good books on bitemporality, Richard Snodgrass'
"Developing time-oriented database applications in SQL" is pretty much the
bible on the subject and goes into great detail. "Managing Time in Relational
Databases" by Tom Johnston and Randall Weis is another great book, a little
more hands on with the code.

I'd love to see someone translate the concepts in both books to a useful
postgres package. As Snodgrass points out, "Unfortunately, due to the
noncompliance of all existing DBMSs, a few of these fragments run on no
existing platform" and thus only discusses how they can be applied to DB2,
MSSQL, Sybase, Oracle8 and UniSQL.

~~~
gen220
At my place of work, we leverage temporal and bitemporal tables in PostgreSQL
extensively (I’d estimate that ⅔ of our Schemas are either temporal or
bitemporal).

We have a few custom tools to make them more ergonomic to create and update,
but the core functionality is derived from an open source extension, I
believe. Although, googling for it now, while away from my work computer, I
can’t seem to find it... :/

... after looking it up on my work computer, it turns out we wrote our own
implementation. It’s less than 200 lines of SQL, and uses three relatively
common extensions. I wish it was open source, because it’d be super helpful to
have in the future. But hey, at least it’s good to know that it can be/has
been practically done?

~~~
snthpy
Anyone know whether the
[pg_bitemporal]([https://github.com/scalegenius/pg_bitemporal](https://github.com/scalegenius/pg_bitemporal))
project is still being developed? There's a talk on YouTube which showed
promise but the Github repo seems pretty dead.

~~~
Hettie
YES! And moreover, I am actively using it in production, we've added lots of
new functions, and made it PG 10 comatiable

~~~
snthpy
Oh great! I enjoyed your talk and look forward to trying it out. I just
briefly browsed the repo and didn't see much in terms of documentation. Where
do you recommend I should start?

------
Sniffnoy
> Why would you stop at two time axes? Why not go for three, or four, or N
> many?

This question is confused, because it's taking a derived property as
fundamental. It takes as fundamental the fact that there are two associated
times, rather than as fundamental the fact that there are _these particular_
associated times, of which there happen to be two. If you can't think of a
_particular_ third associated time to add, the idea of "going to three" is
meaningless on its own.

~~~
refset
My mind immediately jumps to thinking about branching valid time timelines,
but having thought about this a _lot_ over the last few months I think
actually all other kinds of timelines can be implemented using valid time +
additional indexing.

~~~
lichtenberger
I thought about it, too, but I guess then you really dive into the field of
versioning systems and probably it won't be a database system anymore. That is
also manual conflict handling...

I think branching/merging itself wouldn't be that hard to implement, at least
if you have a versioned index at the very core (disclaimer: I'm also
developing an Open Source temporal storage system). But then you'd have
checkouts, handling conflicts...

------
slifin
How does Crux compare to Datomic?

~~~
malcolmjuxt
Crux is quite a different beast to Datomic, in that it is schema-less,
designed to work with Kafka, bi-temporal and some other architectural
differences. Similarities are that they are both built (mainly) in Clojure and
support Datalog queries (albeit different dialects)

Disclosure: I work at JUXT but not directly on Crux

------
skybrian
A toy example of this would be keeping a diary in a text file in a git repo.
The journal entry dates in the text file are independent of the commit dates.

------
Hettie
We've implemented a full Asserted Versioning Framework (Johnson and Weis) for
Postgres, and use it in production. Using it for about 18 months for both OLPT
and OLAP. works beautifully :)

~~~
Hettie
[https://github.com/scalegenius/pg_bitemporal](https://github.com/scalegenius/pg_bitemporal)

------
ToBeBannedSoon
My takeaway is (for applications that need this):

#1: Keep rows immutable. Don't use UPDATE or DELETE.

#2: Store the time columns you need, e.g. insertion or transaction time,
and/or event generation time, etc.

~~~
lichtenberger
Hm, and why storing so much information redundantly? Why not providing a
tailored index structure... basically snapshotting page-trees efficiently :-)

~~~
ToBeBannedSoon
If the db internally supports efficient temporal tracking, then yes by all
means. If it doesn't, then I don't see a way for the developer around the
redundancy.

