Oracle PL/SQL Programming, 5th Edition

Interval Datatypes

The datetime datatypes let you record specific points in time. Interval datatypes, first introduced in Oracle9i Database, are all about recording and computing quantities of time. To better understand what the interval datatypes represent, step back a bit and think about the different kinds of datetime data you deal with on a daily basis:

Instants

An instant is a point in time with respect to a given granularity. When you plan to wake up at a given hour in the morning, that hour represents an instant. The granularity, then, would be to the hour, or possibly to the minute. DATE and all the TIMESTAMP datatypes allow you to represent instants of time.

Intervals

An interval refers not to a specific point in time, but to a specific amount, or quantity, of time. You use intervals all the time in your daily life. You work for eight hours a day (you hope), you take an hour for lunch (in your dreams!), and so forth. Oracle Database’s two INTERVAL types allow you to represent time intervals.

Periods

A period (our definition) refers to an interval of time that begins or ends at a specific instant. For example: “I woke up at 8:00 a.m. today and worked for eight hours.” Here, the 8-hour interval beginning at 8:00 a.m. today would be considered a period. The Oracle database has no datatype to directly support periods, nor does the SQL standard define one.

The database supports two interval datatypes. Both were introduced in Oracle9i Database, and both conform to the ISO SQL standard: ...