In this article I will mention the temporal validity subject which is a new feature that has come with Oracle 12c R1. I hope that will be a beneficial article in terms of awareness.

Temporal validity offers us an infrastructure where we can render the data we have, valid for a particular time range and query our table depended on the time. It’s actually a mechanism that allows us to examine the status of the data in the table or to use it, in any time interval.

Now let’s get to the examples quickly and try to understand the subject.

Firstly let’s talk about the scenario. Let there be an invoice table and a validity interval assigned by us for the every entry in this table. Then let’s change our session parameters according to the validity dates of the inserted entries and query our table in a time period we want.

(You can see the article which includes different uses of syntax of the PERIOD FOR phrase from the links at the end of this article)
We have created the infrastructure where we can query our table according to time with the previous process. Actually Oracle has already created 2 invisible columns in the background using the valid_time name that we had given with the PERIOD FOR phrase. The names of these are;
valid_time_start
valid_time_end
If we query them in the dictionary we will not be able to see their existence as they are invisible.

Now we have provided them with time dimension with the PERIOD FOR phrase, let us update valid_time_start and valid_time_end columns which are created as invisible and whose validity according to time we can adjust, for every entry.

With the line above, we enabled “the 7 minutes ago” in our session. Now let us look at the valid data in that time interval.

SELECT * from hr.invoice;

We listed the entries that were valid 7 minutes ago. From this example, do not think that it’s only possible to go back in time. We can go to a time period where the validity is forward in time and examine the case in that time.

With the methods above, we can query our data however we want. We can even pull the data with the 2nd method and join it with other tables. In addition to that, in Temporal Validity infrastructure, in columns that include unique key constraint in our table, it’s given permission to violation of this constraint while it’s guaranteed that the entries will never overlap in means of time. It means that entries containing the same unique key can be present in the table more than one on condition of all being in different validity periods.