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 Video Library
Refcards
Trend Reports

Events

View Events Video Library

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
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

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

SBOMs are essential to circumventing software supply chain attacks, and they provide visibility into various software components.

Related

  • Decoding Database Speed: Essential Server Resources and Their Impact
  • Understanding Time Series Databases
  • Exploring Data Redaction Enhancements in Oracle Database 23ai
  • The Rise of the Intelligent AI Agent: Revolutionizing Database Management With Agentic DBA

Trending

  • Vibe Coding: Conversational Software Development — Part 1 Introduction
  • Rust: The Must-Adopt Language for Modern Software Development
  • Parallel Data Conflict Resolution in Enterprise Workflows: Pessimistic vs. Optimistic Locking at Scale
  • How to Build a Real API Gateway With Spring Cloud Gateway and Eureka
  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.

By 
Ben Brumm user avatar
Ben Brumm
·
Nov. 25, 16 · Tutorial
Likes (1)
Comment
Save
Tweet
Share
8.4K 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.

Related

  • Decoding Database Speed: Essential Server Resources and Their Impact
  • Understanding Time Series Databases
  • Exploring Data Redaction Enhancements in Oracle Database 23ai
  • The Rise of the Intelligent AI Agent: Revolutionizing Database Management With Agentic DBA

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

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

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • [email protected]

Let's be friends: