Over a million developers have joined DZone.

How to Use the Period Definition in Oracle 12c

See how you can track which of your records are valid in given timeframes with period definition.

· Database Zone

Learn NoSQL for free with hands-on sample code, example queries, tutorials, and more.  Brought to you in partnership with Couchbase.

One of the new features in Oracle 12c is a concept called "period definition" or "temporal validity."

It's a useful feature that makes it easier for you to write queries in certain situations. In this article, I'll explain what this feature is and how you can use it.

What Is This "Period Definition" Feature?

Have you ever seen a database table with records that used effective dates, such as a start date and end date, to indicate if a record was valid?

I've seen this in most databases I've worked on, and I've also created a few that used this feature.

Basically, a table has a start date field and an end date field. These would both be date types.

The fields indicate a date range that this record is valid for.

You would then use these date fields in your queries to work out which record is valid, or the record that has a valid date range.

In Oracle 12c, the feature of "period definition", or "temporal validity" has been introduced.

It makes it easier for Oracle, and you, to track which records are valid on a date.

So, how can you use this feature?

How To Add Period Definition to a Table

First, you need to add a period definition to a table.

Let's say you had a table like this:

CREATE TABLE product (
  product_Id NUMBER,
  product_name VARCHAR2(100),
  price NUMBER(10, 2),
  start_date DATE,
  end_date DATE);


Normally, you would have these two date fields, and you query would need to have a WHERE clause that checked them individually, which makes it more complicated and may reduce performance.

To add this period definition feature to this table, you add a single line to the table definition:

CREATE TABLE product (
  product_Id NUMBER,
  product_name VARCHAR2(100),
  price NUMBER(10, 2),
  start_date DATE,
  end_date DATE
  period for valid (start_date, end_date));


This period for valid line indicates that these are the two dates used to determining if a record is valid.

How Can I Use Period Definition In My Queries?

So, now you have the period for valid keyword in your table. How do you actually use it?

You just add a single line into your SELECT statement.

This might have been your query before:

SELECT product_id, product_name, price, start_date, end_date
FROM product
WHERE start_date <= SYSDATE
AND end_date > SYSDATE;


You can change your query to use the AS OF PERIOD FOR VALID syntax:

SELECT product_id, product_name, price, start_date, end_date
FROM product
AS OF PERIOD FOR VALID SYSDATE;


This query will find records where sysdate is between start_date and end_date.

What About Other Dates?

You don't have to use SYSDATE for this query. You can use any date value. For example, records valid in 30 days time:

SELECT product_id, product_name, price, start_date, end_date
FROM product
AS OF PERIOD FOR VALID SYSDATE + 30;


Or, records valid from yesterday:

SELECT product_id, product_name, price, start_date, end_date
FROM product
AS OF PERIOD FOR VALID SYSDATE - 1;


Or, any date you provide to it:

SELECT product_id, product_name, price, start_date, end_date
FROM product
AS OF PERIOD FOR VALID '01-JAN-2015';


As you can see, this is a pretty simple feature, but it can be quite useful if you work on databases that have data in this format.

The Getting Started with NoSQL Guide will get you hands-on with NoSQL in minutes with no coding needed. Brought to you in partnership with Couchbase.

Topics:
oracle ,sql ,period definition ,database

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}