ORACLE 9I 中的日期格式型別

ORACLE 9I 中的日期格式型別

Date and Time Datatypes
In pre-Oracle9i databases, the only date/time datatype available is DATE,
which stores the date and time. In Oracle9i, the TIMESTAMP and INTERVAL
datatypes have been added to enhance the storage and manipulation of date and
time data. There are six date/time datatypes that can be used for defining
columns in a table:
DATE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND

DATE
Specification DATE
The DATE datatype stores date and time information. You can store the
dates from January 1, 4712 BC to December 31, 9999 AD. If you specify a
date value without the time component, the default time is 12 A.M. (midnight,
00:00:00 hours). If you specify a date value without the date component, the
default value is the first day of the current month. The DATE datatype stores
century, year, month, date, hour, minute, and seconds internally. You can display
the dates in various formats using the NLS_DATE_FORMAT parameter or by
specifying a format mask with the TO_CHAR function. The various date format
masks are discussed in Chapter 3, “Single-Row Functions.”
TIMESTAMP
Specification TIMESTAMP [()]
The TIMESTAMP datatype stores date and time information with fractional
seconds precision. The only difference between the DATE and TIMESTAMP
datatypes is the ability to store fractional seconds up to a precision of nine digits.
The default precision is 6 and can range from 0 to 9. The TIMESTAMP datatype
is new to Oracle9i.

TIMESTAMP WITH TIME ZONE
Specification TIMESTAMP [()] WITH TIME ZONE
The TIMESTAMP WITH TIME ZONE datatype is similar to the TIMESTAMP
datatype, but it stores the time zone displacement. Displacement is
the difference between the local time and the Coordinated Universal Time
(UTC, also known as Greenwich Mean Time). The displacement is represented
in hours and minutes. Two TIMESTAMP WITH TIME ZONE values
are considered identical if they represent the same time in UTC. For example,
5 P.M. CST is equal to 6 P.M. EST or 3 P.M. PST.
TIMESTAMP WITH LOCAL TIME ZONE
Specification TIMESTAMP [()] WITH LOCAL TIME ZONE
The TIMESTAMP WITH LOCAL TIME ZONE datatype is similar to the
TIMESTAMP datatype, but like the TIMESTAMP WITH TIME ZONE
datatype, it also includes the time zone displacement. TIMESTAMP WITH
LOCAL TIME ZONE does not store the displacement information in the
database, but stores the time as a normalized form. of the database time zone.
The data is always stored in the database time zone, but when the user
retrieves data, it is shown in the user’s local session time zone.
The following example demonstrates how the DATE, TIMESTAMP,
TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME
ZONE datatypes store data. The NLS_xx _FORMAT parameter is explicitly set
to display the values in the nondefault format. The data is inserted at CDT
(Central Daylight Time), which is seven hours behind the UTC. (The output
shown for the example was reformatted for better readability.)
SQL> CREATE TABLE date_time_demo (
2 r_no NUMBER (2),
3 c_date DATE DEFAULT SYSDATE,
4 c_timezone TIMESTAMP DEFAULT SYSTIMESTAMP,
5 c_timezone2 TIMESTAMP (2) DEFAULT SYSTIMESTAMP,
6 c_ts_wtz TIMESTAMP (0) WITH TIME ZONE
DEFAULT SYSTIMESTAMP,
7 c_ts_wltz TIMESTAMP (9) WITH LOCAL TIME ZONE
DEFAULT SYSTIMESTAMP)

SQL> /
Table created.
SQL> INSERT INTO date_time_demo (r_no) VALUES (1);
1 row created.
SQL> ALTER SESSION SET NLS_DATE_FORMAT =
'YYYY-MM-DD HH24:MI:SS';
Session altered.
SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT
= 'YYYY-MM-DD HH24:MI:SS.FF';
Session altered.
SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT
= 'YYYY-MM-DD HH24:MI:SS.FFTZH:TZM';
Session altered.
SQL> SELECT * FROM date_time_demo;
R_NO C_DATE C_TIMEZONE
--------------------- ---------------------------
1 2001-10-24 13:09:14 2001-10-24 13:09:14. 000001
C_TIMEZONE2 C_TS_WTZ
---------------------- --------------------------
2001-10-24 13:09:14.00 2001-10-24 13:09:14.-07:00
C_TS_WLTZ
-----------------------------
2001-10-24 13:09:14.000001000
INTERVAL YEAR TO MONTH
Specification INTERVAL YEAR [(precision)] TO MONTH
The INTERVAL YEAR TO MONTH datatype is used to represent a
period of time as years and months. The precision specifies the precision
needed for the year field, and its default is 2. Valid precision values are from
0 to 9. This datatype can be used to store the difference between two date/
time values, where the only significant portions are the year and month.

INTERVAL DAY TO SECOND
Specification INTERVAL DAY [(precision)] TO SECOND
The INTERVAL DAY TO SECOND datatype is used to represent a period
of time as days, hours, minutes, and seconds. The precision specifies the precision
needed for the day field, and its default is 6. Valid precision values are
from 0 to 9. Larger precision values allow a greater difference between the
dates; for example, a precision of 2 allows values from 0 through 99, and a
precision of 4 allows values from 0 through 9999. This datatype can use used
to store the difference between two date/time values, including seconds.
The following example demonstrates the INTERVAL datatypes. We create
a table with the INTERVAL datatypes, insert data to it, and select data from
the table.
SQL> CREATE TABLE interval_demo (
2 ts1 TIMESTAMP (2),
3 iy2m INTERVAL YEAR (3) TO MONTH,
4 id2s INTERVAL DAY (4) TO SECOND);
Table created.
SQL> INSERT INTO interval_demo VALUES (
2 TO_TIMESTAMP('010101-102030.45',
3 'YYMMDD-HH24MISS.FF'),
4 TO_YMINTERVAL('3-7'),
5* TO_DSINTERVAL('4 02:20:30.30'));
1 row created.
SQL> SELECT * FROM interval_demo;
TS1 IY2M ID2S
------------------------- -------- ---------------------
2001-01-01 10:20:30.45 +003-07 +0004 02:20:30.300000
SQL>
Date Arithmetic
Date/time datatypes can be used in expressions with the + or - operator. You
can use the +, -, *, and / operators with the INTERVAL datatypes. Dates are
stored in the database as Julian numbers with a fraction component for the
time. A Julian date refers to the number of days since January 1, 4712 BC.

Due to the time component of the date, comparing dates can result in fractional
differences, even though the date is the same. Oracle provides a number
of functions, such as TRUNC, that help you to remove the time component
when you want to compare only the date portions.
Adding 1 to the date simply moves the date ahead one day. You can add
time to the date by adding a fraction of a day. One day equals 24 hours, or
24 × 60 minutes, or 24 × 60 × 60 seconds. Table 7.1 shows the numbers used
to add or subtract time for a date/time datatype.
Subtracting two dates gives you the difference between the dates in days.
This usually results in a fractional component that represents the time difference.
If the time components are the same, there will be no fractional results.
A date/time value operation using a numeric value results in a date/time
value. The following example adds 2 days and 12 hours to a date value.
SQL> SELECT TO_DATE('2001-10-24 13:09:14') + 2.5
2 FROM dual;
2001-10-27 01:09:14
This example subtracts 6 hours from a timestamp value:
SQL> SELECT TO_TIMESTAMP('2001-10-24 13:09:14.05')
2 - 0.25 FROM dual;
2001-10-24 07:09:14
SQL>
A date/time value subtracted from another date/time value results in a
numeric value (the difference in days). You cannot add two date/time values.
Here is an example that results in the difference between dates as a fraction
of a day:
TABLE 7 . 1 Date Arithmetic
Time to Add or Subtract Fraction Date Difference
1 day 1 1
1 hour 1/24 1/24
1 minute 1/(24 × 60) 1/1440
1 second 1/(24 × 60 × 60) 1/86400

SQL> SELECT SYSDATE,
2 SYSDATE - TO_DATE('2001-10-24 13:09:14')
3 FROM dual;
SYSDATE SYSDATE-TO_DATE('2001-10-2413:09:14')
------------------- -------------------------------------
2001-10-24 15:32:21 .099386574
This example converts the fraction of days to hours, minutes, and seconds
using the NUMTODSINTERVAL function:
SQL> SELECT SYSDATE,
2 NUMTODSINTERVAL(SYSDATE
3 - TO_DATE('2001-10-24 13:09:14'), 'DAY')
4 FROM DUAL;
SYSDATE NUMTODSINTERVAL(SYSDATE
-------------------- -----------------------------
2001-10-24 15:53:04 +000000000 02:43:49.999999999
SQL>
A date/time value operation using an interval value results in a date/time
value. The following example adds 1 year and 3 months to today’s date.
SQL> SELECT TRUNC(SYSDATE),
2 TRUNC(SYSDATE)+TO_YMINTERVAL('1-3')
3 FROM dual;
TRUNC(SYSDATE) TRUNC(SYSDATE)+TO_Y
------------------- -------------------
2001-10-24 00:00:00 2003-01-24 00:00:00
An interval datatype operation on another interval or numeric value
results in an interval value. You can use + and – between two interval
datatypes, and * and / between interval and numeric values. The following
example converts a string (which represents 1 day, 3 hours, and 30 minutes)
to an INTERVAL DAY TO SECOND datatype and multiplies that value by
2, which results in 2 days and 7 hours.
SQL> SELECT TO_DSINTERVAL('1 03:30:00.0') * 2 FROM dual;
TO_DSINTERVAL('103:30:00.0')*2
--------------------------------
+000000002 07:00:00.000000000
SQL>

The following example shows arithmetic between two INTERVAL DAY
TO SECOND datatype values. The interval value of 3 hours and 30 minutes
is subtracted from 1 day, 3 hours, and 30 minutes, resulting in 1 day.
SQL> SELECT TO_DSINTERVAL('1 03:30:00.0')
- TO_DSINTERVAL('0 03:30:00.0') FROM dual;
TO_DSINTERVAL('103:30:00.0')-TO_DSINTERVAL('003:30:00.0')
---------------------------------------------------------
+000000001 00:00:00.000000000
SQL>