What do you know about bitemporal databases?

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/

2. https://opencrux.com/

Has anyone managed to wrap CRUX from python? I want to keep all logic in python ideally. Only thing keeping me away.

There is some other project crux that has a lot of python and this pollutes the Google searches.

I have been experimenting with Crux (https://opencrux.com) and it feels very easy to work with. In past systems once they grew, I always needed to implement audit logs / tables / fields, and this solves that and other problems with respect to having a transaction time, having certain data at a certain time, and being able to update history without losing the history of what was updated.

I think many projects try to re-implement in a ad-hoc way, maybe without knowing it, so if you can start with a bitemporal database it gives a big head start.

You may want to look into datomic or crux.

https://github.com/juxt/crux https://www.datomic.com/

This is one of my favourite topics. Most large DBs I've seen in the wild use some form of temporal tables, but it's often a hacky hand-rolled solution. I'd really like to see more support for bitemporal tables from vendors.

As noted above, the SQL:2011 standard supports bitemporality; here's a good summary from SIGMOD Record: https://cs.ulb.ac.be/public/_media/teaching/infoh415/tempfea...

This is a good summary of vendor support for temporal tables: https://www2.cs.arizona.edu/~rts/sql3.html

Oracle and DB2 both support bitemporal tables, SQL Server only supports system-versioned tables. There is an extension for Postgres that supports system-versioned tables, but it hasn't been updated in 2 years: https://github.com/arkhipov/temporal_tables

In the past, I've manually implemented system-versioning for audit purposes in SQL Server using triggers, T4 templates and the CONTEXT_INFO variable to store user IDs. It worked OK, but schema changes became much more difficult.

Best to use a database with native bi-temporal support, a query planner can get very confused about tables with bi-temporal columns and few constraints, to the extent that it always fails to push the temporal conditions up through the joins resulting in massive unnecessary materialization.

Also - start thinking about archiving now - n lg n is fast at the start - two years later, not so much.

Good luck

For versioning numeric data complications include:

- changing dimensions - handling changing types e.g. A column of strings becoming a compound of floats - efficiently handling appends - efficiently handling corrections of previous data - how to cleanup, remove old versions

The version store storage engine in Arctic provides fast and efficient versioning for numeric data. The code is quite readable so it might be worth having a look at its implementation too.

https://github.com/man-group/arctic/blob/master/README.md

Not sure what language you're planning to use, but if you end up with Java and want an ORM to layer on top of an enterprise-grade database, you could do a lot worse than Reladomo: https://github.com/goldmansachs/reladomo

The ORM has built-in support for various time-series data concepts including bitemporality.

It sounds like you might only need temporal tables and not the whole complication of _bi_temporal tables.

If you just need to get data at a point in time, you only need to store (so called) application-time. If you need to handle both a “created date” and “effective date” type scenario, then you’ll need bitemporality.

Are you trying to build a backtesting framework?

I've come up with a solution specifically for pandas dataframes. Not sure how similar a solution would be for your use case.

It's funny, I've never heard that term but looking it up, I find some connections to some things I'm working on (that are not bitemporal databases).

What are some good resources?

A spreadsheet is a bitemporal database of sorts right?

Isn't this just temporal tables in SQL Server?

I use django for my day to day. I understand its "migration" https://docs.djangoproject.com/en/dev/topics/migrations as a kind of bitemporal database. Would you mind providing more context about your use case so maybe more concrete consideration can be discussed?

https://opencrux.com/

Is this different than a relational database with slowly changing dimensions?

Check out "datomic".

There was mentat. Almost.