oracle日期时间类型

Oracle 数据类型及存储方式(三)日期时间类型——袁光东[转]

2009年10月18日星期日 16:57

第三部分日期时间类型

§3.1 DATE

Date类型Oralce用于表示日期和时间的数据类型。固定占用7个字节。

包括七个属性：

世纪

世纪中的年份

月份

月份中的哪一天

小时

分

秒

SQL> create table test_date(col_date date);

Table created

SQL> insert into test_date values(to_date('2008-06-27

10:35:00','yyyy-mm-dd hh24:mi:ss'));

1 row inserted

SQL> 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,1

Date类型的内部编码为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 inserted

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

SQL> insert into test_date1 values('full',to_date('2008-06-27

12:01:00','yyyy-mm-dd hh24:mi:ss'));

1 row inserted

SQL> insert into test_date1(col_char,col_date) select 'minute',

trunc(col_date,'mi') from test_date1

2 union all

3 select 'day', trunc(col_date,'dd') from test_date1

4 union all

5 select 'month',trunc(col_date,'mm') from test_date1

6 union all

7 select 'year',trunc(col_date,'y') from test_date1

8 ;

4 rows inserted

SQL> 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,1

minute 2008-6-27 1 Typ=12 Len=7: 120,108,6,27,13,2,1

day 2008-6-27 Typ=12 Len=7: 120,108,6,27,1,1,1

month 2008-6-1 Typ=12 Len=7: 120,108,6,1,1,1,1

year 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);