10.1.1.1 Declaring date variables

The syntax for declaring DATE variables is as follows:

var_name [CONSTANT] DATE [:= | DEFAULT initial_value]

In this syntax, var_name is the name of the variable you are declaring. Once you declare a DATE variable, you can use it to store just date values, or combined date and time values.

Datetime values always have a time component. The question is whether you ignore it or use it.

Following are some examples of DATE declarations:

DECLARE

todays_date CONSTANT DATE := SYSDATE;

hire_date DATE DEFAULT SYSDATE;

end_of_year DATE := TO_DATE('12/31/2002', 'MM/DD/YYYY');

10.1.1.2 When to use date

Use DATE if you're developing programs for existing systems that use DATE columns within the database. Use DATE if you expect to deploy an application against a pre-Oracle9i database. And, of course, you'll have to use DATE if you are currently working with Oracle8i, Oracle8, or some prior release. But for new development that will be deployed only against Oracle9i or higher, consider using one of the new TIMESTAMP datatypes.

10.1.1.3 Limitations of date

DATE has two limitations you should be aware of:

• The DATE datatype does not resolve time to anything less than increments of a single second. Thus, DATE is not very useful for tracking real-time activities that occur at subsecond intervals.

• DATE values do not include time zone information. This can be a problem if users of your system span multiple time zones. When you retrieve a DATE value from the database, you have to "just know" what time zone it represents.

The newer TIMESTAMP datatypes overcome these limitations, allowing you to resolve time down to the billionth of a second and to store time zone information with your datetime values.

If you are using a release prior to Oracle9i and you need to track time at subsecond intervals, you can store the information as a number. You can obtain subsecond timings using the DBMS_UTILITY package's GET_TIME function.

10.1.2 The timestamp Datatypes

Oracle9iintroduced three TIMESTAMP datatypes. All three allow for time to be resolved to the billionth of a second; two accommodate time zones, but in different ways. The three types are:

TIMESTAMP

Stores date and time without respect to time zone. Except for being able to resolve time to the billionth of a second, TIMESTAMP is the equivalent of DATE.

TIMESTAMP WITH TIME ZONE

Stores the time zone along with each date and time value.

TIMESTAMP WITH LOCAL TIME ZONE

Stores a date and time that is assumed to be in the local time zone. For database columns of this type, the "local" time zone is the database time zone. For PL/SQL variables of this type, the "local" time zone is the session time zone. Values that move between database columns and PL/SQL variables are converted from one time zone into the other.

The nuances of these three types, especially the TIMESTAMP WITH LOCAL TIME ZONE type, can be a bit difficult to understand at first. Figure 10-1 shows the effects of the different datetime datatypes on a datetime value as it moves from a user in one time zone through the database to another user in a different time zone.