Oracle 数据类型及存储方式(三)日期时间类型——袁光东[转]2009年10月18日星期日 16:57第三部分日期时间类型§3.1 DATEDate类型Oralce用于表示日期和时间的数据类型。
固定占用7个字节。
包括七个属性：世纪世纪中的年份月份月份中的哪一天小时分秒SQL> create table test_date(col_date date);Table createdSQL> insert into test_date values(to_date('2008-06-2710:35:00','yyyy-mm-dd hh24:mi:ss'));1 row insertedSQL> select to_char(col_date,'yyyy-mm-dd hh24:mi:ss'),dump(col_date) from test_date;TO_CHAR(COL_DATE,'YYYY-MM-DDHH DUMP(COL_DATE)--------------------------------------------------------------------------------------------------------------2008-06-27 10:35:00 Typ=12 Len=7: 120,108,6,27,11,36,1Date类型的内部编码为12长度:占用7个字节数据存储的每一位到第七位分别为：世纪，年，月，日，时，分，秒。
世纪：采用”加100”表示法来存储。
即世纪+100来存储。
120 – 100 = 20 年:跟世纪一样采用”加100”表示法来存储。
108 – 100 = 08(采用两位表示) 月:自然存储.6日:自然存储，不做修改，27时：（时，分，秒都采用“加1”法存储）11 -1= 10分:36 -1 = 35秒:1 -1 = 0为什么世纪和年份要用加100法存储呢？是为了支持BC和AD日期。
BC即为公元前。
AD即为公元。
如果世纪– 100为一个负数，那么就是一个BC日期。
插入一个公元前日期SQL> insert into test_date values(to_date('-4712-01-01','syyyy-mm-dd hh24:mi:ss'));1 row insertedSQL> select to_char(col_date,'bc yyyy-mm-dd hh24:mi:ss'),dump(col_date) from test_date;TO_CHAR(COL_DATE,'BCYYYY-MM-DD DUMP(COL_DATE)--------------------------------------------------------------------------------------------------------------公元 2008-06-27 10:35:00 Typ=12 Len=7: 120,108,6,27,11,36,1公元前 4712-01-01 00:00:00 Typ=12 Len=7: 53,88,1,1,1,1,1我们已经了解了日期的存储结构。
当要对日期进行截取时，比如去掉时，分，秒。
只需要把最后的三个字节设为：12 12 1就可以了。
SQL> create table test_date1 (col_char varchar2(12), col_date date);Table createdSQL> insert into test_date1 values('full',to_date('2008-06-2712:01:00','yyyy-mm-dd hh24:mi:ss'));1 row insertedSQL> insert into test_date1(col_char,col_date) select 'minute',trunc(col_date,'mi') from test_date12 union all3 select 'day', trunc(col_date,'dd') from test_date14 union all5 select 'month',trunc(col_date,'mm') from test_date16 union all7 select 'year',trunc(col_date,'y') from test_date18 ;4 rows insertedSQL> select col_char, col_date,dump(col_date) from test_date1;COL_CHAR COL_DATE DUMP(COL_DATE)------------ -------------------------------------------------------------------------------------------full 2008-6-27 1 Typ=12 Len=7: 120,108,6,27,13,2,1minute 2008-6-27 1 Typ=12 Len=7: 120,108,6,27,13,2,1day 2008-6-27 Typ=12 Len=7: 120,108,6,27,1,1,1month 2008-6-1 Typ=12 Len=7: 120,108,6,1,1,1,1year 2008-1-1 Typ=12 Len=7: 120,108,1,1,1,1,1要把一个日期截取，只取到年。
数据库只是把最后5个字节置上1。
这是非常快的。
当我们对一个Date字段进行操作，需要截取到年份进行比较时，我们经常使用to_char函数。
通过会这样写。
Select * from test_date1 where to_char(col_date ,’yyyy’) = ‘2008’ 而不是Select * from test_date1 where trunc(col_date,’y’) =to_date(‘2008-01-01’,’yyyy-mm-dd’)使用trunc会占用更少的资源，性能更优。
使用to_char所有的CPU时间与trunc相差一个数量级，差不多10倍。
因为to_char必须把日期转换成一个串，并利用当前系统所采用的NLS来完成，然后执行一个串与串的比较。
而TRUNC只需要把后5个字节设置为1，然后将两个7位的字节的二进行数进行比较就搞定了。
所要截取一个DATE列叶，应该避免使用to_char.另外，要完全避免对DATE列应用函数。
比如我们要查询2008年的所有数据，并且这一列上也有索引，我们希望能够用上这个索引。
SQL> select count(col_date) from test_date1 where col_date >=to_date('2008-01-01','yyyy-mm-dd') and col_date <to_date('2009-01-01','yyyy-mm-dd');COUNT(COL_DATE)---------------5§3.2 向Date类型增加或减时间怎么向Date类型增加时间，例如：向Date增加1天，或1小时，或1秒，一月等。
常有的办法有几个方法：a.向date增加一个NUMBER值。
因为Date 加减操作是以天为单位。
1秒就是1/24/60/60。
依此类推。
b.使用INTERVAL类型。
后续会介绍c.使用内置函数add_months增加月。
增加月不像增加天那么简单，所以需要使用内置函数来处理。
3.2.1 增加秒SQL> create table test_date2(id varchar2(10), operate_time date);Table createdSQL> insert into test_date2 values('1',sysdate);1 row insertedSQL> select id, to_char(operate_time, 'yyyy-mm-dd hh24:mi:ss') from test_date2 where id=1;ID TO_CHAR(OPERATE_TIME,'YYYY-MM----------- ------------------------------1 2008-06-27 13:35:35SQL> update test_date2 set operate_time = operate_time + 1/24/60/60 where id=1;1 row updatedSQL> select id, to_char(operate_time, 'yyyy-mm-dd hh24:mi:ss') from test_date2 where id=1;ID TO_CHAR(OPERATE_TIME,'YYYY-MM----------- ------------------------------1 2008-06-27 13:35:363.2.2 增加分SQL> update test_date2 set operate_time = operate_time + 1/24/60 where id=1;1 row updatedSQL> select id, to_char(operate_time, 'yyyy-mm-dd hh24:mi:ss') from test_date2 where id=1;ID TO_CHAR(OPERATE_TIME,'YYYY-MM----------- ------------------------------1 2008-06-27 13:36:363.2.3 增加小时SQL> update test_date2 set operate_time = operate_time + 1/24 where id=1;1 row updatedSQL> select id, to_char(operate_time, 'yyyy-mm-dd hh24:mi:ss') from test_date2 where id=1;ID TO_CHAR(OPERATE_TIME,'YYYY-MM----------- ------------------------------1 2008-06-27 14:36:363.2.4 增加天SQL> update test_date2 set operate_time = operate_time + 1 where id=1;1 row updatedSQL> select id, to_char(operate_time, 'yyyy-mm-dd hh24:mi:ss') from test_date2 where id=1;ID TO_CHAR(OPERATE_TIME,'YYYY-MM----------- ------------------------------1 2008-06-28 14:36:363.2.4 增加周SQL> update test_date2 set operate_time = operate_time + 1 * 7 where id=1;1 row updatedSQL> select id, to_char(operate_time, 'yyyy-mm-dd hh24:mi:ss') from test_date2 where id=1;ID TO_CHAR(OPERATE_TIME,'YYYY-MM----------- ------------------------------1 2008-07-05 14:36:363.2.5 增加月SQL> update test_date2 set operate_time = add_months(operate_time,1) where id=1;1 row updatedSQL> select id, to_char(operate_time, 'yyyy-mm-dd hh24:mi:ss') from test_date2 where id=1;ID TO_CHAR(OPERATE_TIME,'YYYY-MM----------- ------------------------------1 2008-08-05 14:36:363.2.6 增加年SQL> update test_date2 set operate_time = add_months(operate_time,1 * 12) where id=1;1 row updatedSQL> select id, to_char(operate_time, 'yyyy-mm-dd hh24:mi:ss') from test_date2 where id=1;ID TO_CHAR(OPERATE_TIME,'YYYY-MM----------- ------------------------------1 2009-08-05 14:36:36另外可以使用一个非常有用的函数NUMTODSINTERVAL来新增加小时，分钟，秒。