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.