What problem does this solve?
Temporal tables adding an additional "time axis" to SQL databases. A "valid from" and "valid to" field is added to each row and the SQL syntax is extended for allowing two new types of queries:
1. Query the data as of a specific point in time. E.g. "show all customers as of April 9th 2021". This not only limits WHICH customers you see, but also select the exact state of each customer as valid at this point in time. For example if the adress of a customer changed on April 10th, you will get the old address.
2. Query all versions of a specific entity. This makes tracking of changes or time series analysis possible.
This feature is transparent, so if you use "normal" SQL queries, you keep getting the current state of the data. I don't know if this is true for this PostgreSQL extension, but MariaDB even hides the "valid from" and "valid to" columns and only show them when you explicitly select them.
Additionally there a two types of "valid from"/"valid to" data, which can exist at the same time:
1. Application period: Those validity dates represent a period in the real world. If we stay at the customer address example, they can express "a customer informed me, that their addess will change on April 15th, so the current address is valid until then and the new address is valid from then".
2. System period (also called transaction time): Those validity dates are a kind of technical period. They represent when data changed in the database, e.g. the exact point in time when an UPDATE query was executed.
We have tons of that at work, so this feature would have been nice. Currency exchange rates, dozens of official code lists (including countries!), VAT registration status of companies.
If a user makes a change to a declaration submitted at an earlier date, then the data from the original submission date must be used, so we need to keep all this around.
Alas, not using PostgreSQL.
I know we have plans to start adding SQL Server support this year (due to customer demand), and we might end up doing a full transition. Stuff like this certainly doesn't make that case weaker.
One useful example is continuous forecasting. Every forecast is done with a certain moving window of historical training data, and each time the forecast is run, this training data is different.
In order to evaluate/backtest the performance of the forecasting algorithm over time, you need to be able to retrieve all the previous training datasets from the database, calculate a performance metric, and then aggregate. This lets you do that on a continuous basis in the database itself, without dumping each training set to secondary storage each time.
Also, in many real life applications, historical data isn’t immutable — data corrections can arrive after the fact that will change state, so it’s usually not sufficient to just use a simple WHERE clause to retrieve a particular time range. This is where time travel becomes really useful.
App period solves the problem of https://en.wikipedia.org/wiki/Slowly_changing_dimension