Working with Dates in PL/SQL（PL/SQL中使用日期）
The previous articles in this introductory PL/SQL series focused on working with strings and numbers in PL/SQL-based applications. Without a doubt, strings and numbers are important, but it is certainly a very rare application that does not also rely on dates. You need to keep track of when events occurred, when people were born, and much more.
As a result, you will quite often need to
Declare variables and constants for dates
Use built-in functions to display and modify date values
Perform computations on dates
A date is also a considerably more complex datatype than a string or a number. It has multiple parts (year, month, day, hour, and so on), and there are many rules about what constitutes a valid date. This article gives you all the information you need in order to begin working with dates in your PL/SQL programs.
Dates, Time Stamps, and Intervals in PL/SQL
Most applications require the storage and manipulation of dates and times. Unlike strings and numbers, dates are quite complicated: not only are they highly formatted data, but there are also many rules for determining valid values and valid calculations (leap days and years, daylight saving time changes, national and company holidays, date ranges, and so on).
Fortunately,
Oracle Database and PL/SQL provide a set of true date and time datatypes that store both date and time information in a standard internal format, and they also have an extensive set of built-in functions for manipulating the date and time.
There are three datatypes you can use to work with dates and times:
DATE—This datatype stores a date and a time, resolved to the second. It does not include the time zone. DATE is the oldest and most commonly used datatype for working with dates in Oracle applications.
TIMESTAMP—Time stamps are similar to dates, but with these two key distinctions: (1) you can store and manipulate times resolved to the nearest billionth of a second (9 decimal places of precision), and (2) you can associate a time zone with a time stamp, and Oracle Database will take that time zone into account when manipulating the time stamp.
INTERVAL—Whereas DATE and TIMESTAMP record a specific point in time, INTERVAL records and computes a time duration. You can specify an interval in terms of years and months, or days and seconds.
Listing 1 includes example variables whose declaration is based on these datatypes.
Code Listing 1: Declaring DATE, TIMESTAMP, and INTERVAL variables
DECLARE
l_today_date DATE := SYSDATE;
l_today_timestamp TIMESTAMP := SYSTIMESTAMP;
l_today_timetzone TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;
l_interval1 INTERVAL YEAR (4) TO MONTH := '2011-11';
l_interval2 INTERVAL DAY (2) TO SECOND := '15 00:30:44';
BEGIN
null;
END;
Working with intervals and time stamps with time zones can be very complicated; relatively few developers will need these more advanced features. This article focuses on the core DATE and TIMESTAMP types, along with the most commonly used built-in functions.
Choosing a datatype. With such an abundance of riches, how do you decide which of these date-and-time datatypes to use Here are some guidelines:
Use one of the TIMESTAMP types if you need to track time down to a fraction of a second.
You can, in general, use TIMESTAMP in place of DATE. A time stamp that does not contain subsecond precision takes up 7 bytes of storage, just as a DATE datatype does. When