1、Using Data TypesWhat Will I Learn?Create a table using TIMESTAMP and TIMESTAMP WITH TIME ZONE column data typesCreate a table using INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND column data typesGive examples of organizations and personal situations where it is important to know to which time zo

2、ne a date-time value refers.Why Learn It?If you ever travel to another country, youll quickly find out that the money in your pocket may not be that of the local currency. If you want to buy something, it will be necessary to convert your money into the currency of the local country.This conversion

3、process is a lot like dealing with data types in SQL. Different types of data have different types of characteristics, the purpose of which is to efficiently store data. In this lesson, you will learn more about data types and their uses.Data typeEach value manipulated by Oracle has a data type.A va

4、lues data type associates a fixed set of properties with the value. These properties cause the database to treat values of one data type differently from values of another.Data typeDifferent data types offer several advantages:Columns of a single type produce consistent results. For example, DATE da

5、ta type columns always produce date values.You cannot insert the wrong type of data into a column. For example, columns of data type DATE will prevent NUMBER type data from being inserted.For these reasons, each column in a relational database can hold only one type of data. You cannot mix data type

6、s within a column.Most common data typesFor character values: CHAR (fixed size,maximum 2000 characters); VARCHAR2(variable size, maximum 4000 characters);CLOB (variable size, maximum 4 billion characters)For number values: NUMBER (variable size,maximum precision 38 digits)For date and time values: D

7、ATE, TIMESTAMP., INTERVALFor binary values (eg multimedia: JPG, WAV, MP3 and so on): RAW (variable size, maximum 2000 bytes); BLOB(variable size, maximum 4 billion bytes).Most common data typesFor character values, it is usually better to use VARCHAR2 or CLOB than CHAR, because it saves space and is

8、 faster. For example, an employees last name is Chang. In a VARCHAR2(30) column, only the 5 significant characters are stored: C h a n g. But in a CHAR(30) column, 25 trailing spaces would be stored as well, to make a fixed size of 30 characters.Number values can be negative as well as positive. For

9、 example, NUMBER(6,2) can store any value from +9999.99 down to 9999.99.DATE-TIME DATA TYPESThe DATE data type stores a value of centuries down to whole seconds, but cannot store fractions of a second. 21-AUG-2003 17:25:30 is a valid value, but 21-AUG-2003 17:25:30.255 is not.The TIMESTAMP data type

10、 is an extension of the DATE data type which allows fractions of a second.For example, TIMESTAMP(3) allows 3 digits after the whole seconds, allowing values down to milliseconds to be stored.select systimestamp from dual;TIMESTAMP . WITH LOCAL TIME ZONEThink about the time value 17:30. Of course it

11、means “half past five in the afternoon”.But where in the world? Is it half past five New York City time, or Beijing time, or Istanbul time, or . ?In todays globalized organizations which operate in many different countries, it is important to know which time zone a date-time value refers to.TIMESTAM

12、P . WITH LOCAL TIME ZONETIMESTAMP WITH TIME ZONE stores a time zone value as a displacement from Universal Coordinated Time or UCT (previously known as Greenwich Mean Time or GMT).For example, a value of 21-AUG-03 08:00:00 5:00 means 8:00 am 5 hours behind UTC. This is US Eastern Standard Time (EST)

13、.TIMESTAMP WITH LOCAL TIME ZONE is the same, but with one difference:when this column is SELECTed in a SQL statement, the time is automatically converted to the selecting users time zone.TIMESTAMP . WITH LOCAL TIME ZONECREATE TABLE time_example(first_column TIMESTAMP WITH TIME ZONE,second_column TIM

14、ESTAMP WITH LOCAL TIME ZONE); INSERT INTO time_example (first_column, second_column) VALUES( TO_TIMESTAMP_TZ(15-11-2003 08:00:00 -5:00,DD-MM-YYYY HH24:MI:SS TZH:TZM),TO_TIMESTAMP_TZ(15-11-2003 08:00:00 -5:00,DD-MM-YYYY HH24:MI:SS TZH:TZM) );Both values are stored with a time displacement of5 hours (

15、EST).TIMESTAMP . WITH LOCAL TIME ZONEBut now we executes:SELECT * FROM time_example;Our time is 5 hours ahead of EST; when its 8am in NewYork City, its 1pm here.SELECT first_column-second_column FROM time_example;INTERVAL DATA TYPESThese store the elapsed time, or interval of time, between two date-

16、time values.INTERVAL YEAR TO MONTH stores a period of time measured in years and months.INTERVAL DAY TO SECOND stores a period of time measured in days, hours, minutes and seconds.The data type syntax is:INTERVAL YEAR(year_precision) TO MONTHyear_precision is the maximum number of digits in the YEAR

17、 element. The default value of year_precision is 2.This example shows an interval of 120 months,INTERVAL DATA TYPESCREATE TABLE time_example2(loan_duration INTERVAL YEAR(3) TO MONTH);INSERT INTO time_example2 (loan_duration)VALUES (INTERVAL 120 MONTH(3);SELECT TO_CHAR (to_date(26-09-2005,DD-MM-YYYY)

18、+loan_duration,dd-mon-yyyy)FROM time_example2;INTERVAL DAY TO SECONDUse this when you need a more precise difference between two date-time values.The data type syntax is:INTERVAL DAY(day_precision) TO SECOND (fractional_seconds_precision) day_precision is the maximum number of digits in the DAY datetime field.The default is 2.fractional_seconds_precision is the number of digits in the fractional part of the SECOND date-time field.The default is 6.INTERVAL DAY TO SECONDCREATE TABLE time_example3(day_dura