Working with Dates in PL/SQL（PL/SQL中使用日期）(二)

Use TIMESTAMP WITH TIME ZONE if you need to keep track of the session time zone in which the data was entered.

Use TIMESTAMP WITH LOCAL TIME ZONE if you want the database to automatically convert a time between the database and session time zones.

Use DATE when it’s necessary to maintain compatibility with an existing application written before any of the TIMESTAMP datatypes were introduced.

Use datatypes in your PL/SQL code that correspond to, or are at least compatible with, the underlying database tables. Think twice, for example, before reading a TIMESTAMP value from a table into a DATE variable, because you might lose information (in this case, the fractional seconds and perhaps the time zone).

Getting the current date and time. PL/SQL developers often need to retrieve and work with the current date and time. Most developers use the classic SYSDATE function, but Oracle Database now offers several functions to provide variations of this information, as shown in Table 1.

Function Time Zone Datatype Returned

CURRENT_DATE Session DATE

CURRENT_TIMESTAMP Session TIMESTAMP WITH TIME ZONE

LOCALTIMESTAMP Session TIMESTAMP

SYSDATE Database server DATE

SYSTIMESTAMP Database server TIMESTAMP WITH TIME ZONE

Table 1: SYSDATE and other options for working with the current date and time

Listing 2 displays the values returned by calls to SYSDATE and SYSTIMESTAMP.

Code Listing 2: Calls to SYSDATE and SYSTIMESTAMP and the returned values

BEGIN

DBMS_OUTPUT.put_line (SYSDATE);

DBMS_OUTPUT.put_line (SYSTIMESTAMP);

DBMS_OUTPUT.put_line (SYSDATE - SYSTIMESTAMP);

END;

/

Here is the output:

07-AUG-11

07-AUG-11 08.46.16.379000000 AM -05:00

-000000000 00:00:00.379000000

Because I have passed dates and time stamps to DBMS_OUTPUT.PUT_LINE, Oracle Database implicitly converts them to strings, using the default format masks for the database or the session (as specified by the National Language Settings NLS_DATE_FORMAT parameter). A default installation of Oracle Database sets the default DATE format to DD-MON-YYYY. The default TIMESTAMP format includes both the date offset and the time zone offset.

Note that it is possible to perform date arithmetic: I subtract the value returned by SYSTIMESTAMP from the value returned by SYSDATE. The result is an interval that is very close (but not quite equal) to zero.

Converting dates to strings and strings to dates. As with TO_CHAR for numbers, you use another version of the TO_CHAR function to convert a date or a time stamp to a string. And, again as with numbers, Oracle Database offers a large set of format elements to help you tweak that string so it appears exactly as you need it. Here are some examples:

Use TO_CHAR without a format mask. If you do not include a format mask, the string returned by TO_CHAR will be the same as that returned when Oracle Database performs an implicit conversion:

BEGIN

DBMS_OUTPUT.put_line (

TO_CHAR (SYSDATE));

DBMS_OUTPUT.put_line (

TO_CHAR (SYSTIMESTAMP));

END;

/

07-AUG-11

07-AUG-11 08.55.00.470000000 AM -05:00

Use TO_CHAR to display the full names of both the day and the month in the date:

BEGIN

DBMS_OUTPUT.put_line (

TO_CHAR (SYSDATE,

'Day, DDth Month YYYY'));

END;

/

Sunday , 07TH August 2011

Note: The language used to di