DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
11 Monitoring and Observability Tools for 2023
Learn more
  1. DZone
  2. Data Engineering
  3. Databases
  4. How to Use the Period Definition in Oracle 12c

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.

Ben Brumm user avatar by
Ben Brumm
·
Nov. 25, 16 · Tutorial
Like (1)
Save
Tweet
Share
6.77K Views

Join the DZone community and get the full member experience.

Join For Free

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.

Database

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • DevOps vs Agile: Which Approach Will Win the Battle for Efficiency?
  • How To Use Linux Containers
  • Running Databases on Kubernetes
  • How To Handle Secrets in Docker

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: