Oracle Database XE Datatypes: Datetime and Large Object
(Page 1 of 2 )
In this third part of a seven-part article series on Oracle Database XE, you'll continue learning about Oracle's built-in datatypes. This time we'll be covering datetime and large object datatypes. This article is excerpted from chapter 30 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
Datetime Datatypes
Oracle Database XE stores dates as both point-in-time values (DATEandTIMESTAMP) and as periods of time (INTERVAL). TheDATEdatatype stores the four-digit year, month, day, hours, minutes, and seconds. TheTIMESTAMPdatatype expands the precision of date values to billionths of a second (0.000000001 second). For ease of use, Oracle defines a standard date format of DD-MON-YY so you don’t always have to use date conversion functions to convert date values in string constants toDATETIMEvalues (we show you how to use date conversion and other functions in Chapter 35). So in this example, you do not have to explicitly convert the date string:
insert into customer_comment(customer_name, comment_date, comment_text) values('Suzie Pustina','15-aug-06', 'Best service I''ve ever received from any technician.');
In this example, however, the date format in the string is not the default, so you must use theTO_DATEconversion function:
insert into customer_comment(customer_name, comment_date, comment_text) values('Ann Vandross',to_date('August 29, 2006','MONTH DD, YYYY'), 'Not sure if I will shop at your store again.');
Querying the table, you see that both dates are stored correctly in the table:
select customer_name, comment_date from customer_comment;
--------------------------------------------
CUSTOMER_NAME COMMENT_DATE ------------------------ ------------ Suzie Pustina 15-AUG-06 Ann Vandross 29-AUG-06
--------------------------------------------
Note You can change the default date format in your database by changing the value of the initialization parameterNLS_DATE_FORMAT.
Table 30-4 lists the datetime datatypes available in Oracle Database XE.
Table 30-4. Datetime Datatypes
Datetime Datatype Name
Description
DATE
Stores a date and time with a one-second precision. The date portion can be between January 1, 4712, BCE (Before Common Era) through December 31, 4712, CE (Common Era). If you do not specify a time, it defaults to midnight.
TIMESTAMP[(precision)]
Stores date and time with subsecond precision, up to nine digits after the decimal point (one-billionth of a second). The date portion has the same range as DATE. The precision defaults to 6 and can range from 0 to 9.
TIMESTAMP[(precision)]
WITH TIMEZONE
Same as TIMESTAMP but also stores a time zone offset. The time zone offset defines the difference, in hours and minutes, between the local time zone and Coordinated Universal Time (UTC, also known as Greenwich Mean Time, or GMT). Two different columns defined as TIMESTAMP WITH TIMEZONE are considered equal if they represent the same absolute time. For example, 10:00 a.m. MST is equal to 11:00 a.m. CST.
TIMESTAMP[(precision)]
WITH LOCAL TIMEZONE
Same as TIMESTAMPbut when inserted into a table column it is converted from the local time to the database time zone. When the value is retrieved from the table column, the value is converted from the database time zone to the local time zone.
Table 30-4. Datetime Datatypes (continued)
Datetime Datatype Name
Description
INTERVAL YEAR[(precision)] TO MONTH
Stores a period of time in years and months. The precision is the maximum number of digits required for the year portion of the time interval and defaults to 2. You use this datatype to store the difference between two datetime values if you require yearly or monthly granularity.
INTERVAL DAY[(d_precision)] TO SECOND[(s_precision)]
Stores a period of time in days, hours, minutes, and seconds. The value ofd_precisionis the maximum number of digits required for the day portion of the period; similarly, the value ofs_precisionis the maximum number of digits to the right of the decimal point required for the second portion of the period.
