Home arrow Miscellaneous arrow Oracle Database XE Datatypes: Datetime...

Oracle Database XE Datatypes: Datetime and Large Object
By: Apress Publishing
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 5 stars5 stars5 stars5 stars5 stars / 1

    Table of Contents:
  • Oracle Database XE Datatypes: Datetime and Large Object
  • Large Object Datatypes



    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:

    create table customer_comment (
    customer_name varchar2(100),
    comment_date  date,
    comment_text  varchar2(500));

    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;


    ------------------------ ------------
    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



    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.


    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.



    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.



    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.



    More Miscellaneous Articles
    More By Apress Publishing

    blog comments powered by Disqus


    - Attention: Forum and Site Maintenance
    - Oracle Database XE: Indexes and Sequences
    - Modifying Tables in Oracle Database XE
    - Oracle Database XE: Tables and Constraints
    - More on Oracle Databases and Datatypes
    - Oracle Database XE Datatypes: Datetime and L...
    - Oracle Database XE Datatypes: Character and ...
    - From Databases to Datatypes
    - Firefox 3.6.6 Released with Improved Plug-in...
    - Attention Bloggers: WordPress 3.0 Now Releas...
    - Reflection in PHP 5
    - Inheritance and Other Advanced OOP Features
    - Advanced OOP Features
    - Linux from Scratch V.6.6 Review
    - Linux Gaining in Strength

    Developer Shed Affiliates


    © 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap