oracle 数据类型详解

oracle 数据类型详解---日期型 oracle数据类型看起来非常简单，但用起来会发现有许多知识点，本文是我对ORACLE日期数据类型的一些整理，都是开发入门资料，与大家分享： 注：由于INTERVAL及TIME ZONE实际用得比较少，所以本文内容未涉及这两个方面。 1、常用

oracle 数据类型详解---日期型

 

      oracle数据类型看起来非常简单，但用起来会发现有许多知识点，本文是我对ORACLE日期数据类型的一些整理，都是开发入门资料，与大家分享：
注：由于INTERVAL及TIME ZONE实际用得比较少，所以本文内容未涉及这两个方面。

1、常用日期型数据类型
1.1、DATE
这是ORACLE最常用的日期类型，它可以保存日期和时间，常用日期处理都可以采用这种类型。DATE表示的日期范围可以是公元前4712年1月1日至公元9999年12月31日
date类型在数据库中的存储固定为7个字节，格式为：
 第1字节：世纪+100
 第2字节：年
 第3字节：月
 第4字节：天
 第5字节：小时+1
 第6字节：分+1
 第7字节：秒+1

1.2、TIMESTAMP(p)
这也是ORACLE常用的日期类型，它与date的区别是不仅可以保存日期和时间，还能保存小数秒，小数位数可以指定为0-9，默认为6位，所以最高精度可以到ns(纳秒)，数据库内部用7或者11个字节存储，如果精度为0，则用7字节存储，与date类型功能相同，如果精度大于0则用11字节存储。
格式为：
 第1字节：世纪+100
 第2字节：年
 第3字节：月
 第4字节：天
 第5字节：小时+1
 第6字节：分+1
 第7字节：秒+1
 第8-11字节：纳秒，采用4个字节存储，内部运算类型为整形

注：TIMESTAMP日期类型如果与数值进行加减运算会自动转换为DATE型，也就是说小数秒会自动去除。

1.3、DATE与TIMESTAMP类型内部存储验证

<span> 1</span> <span>create</span> <span>table</span> T
<span> 2</span> (
<span> 3</span>   C1 DATE,
<span> 4</span>   C2 <span>TIMESTAMP</span>(<span>9</span>)
<span> 5</span> );
<span> 6</span> 
<span> 7</span> <span>insert</span> <span>into</span> t(c1,c2) <span>values</span>(date<span>'</span><span>2010-2-12</span><span>'</span>,<span>timestamp</span><span>'</span><span>2010-2-12 13:24:52.234123211</span><span>'</span>);
<span> 8</span> <span>insert</span> <span>into</span> t(c1,c2) <span>values</span>(
<span> 9</span>        to_date(<span>'</span><span>2010-2-12 10:20:30</span><span>'</span>,<span>'</span><span>YYYY-MM-DD HH24:MI:SS</span><span>'</span>),
<span>10</span>        to_timestamp(<span>'</span><span>2010-2-12 13:24:52.123456</span><span>'</span>,<span>'</span><span>YYYY-MM-DD HH24:MI:SS.FF6</span><span>'</span>)
<span>11</span> );
<span>12</span> 
<span>13</span> SQL<span>></span> <span>select</span> c1,<span>dump</span>(c1) c1_d,c2,<span>dump</span>(c2) c2_d <span>from</span> t;

C1                       C1_D                                     C2                                       C2_D
------------------------ ---------------------------------------- ---------------------------------------- -----------------------------------------------------
2010-2-12                Typ=12 Len=7: 120,110,2,12,1,1,1         12-FEB-10 01.24.52.234123211 PM          Typ=180 Len=11: 120,110,2,12,14,25,53,13,244,111,203
2010-2-12 上午 10:20:30  Typ=12 Len=7: 120,110,2,12,11,21,31      12-FEB-10 01.24.52.123456000 PM          Typ=180 Len=11: 120,110,2,12,14,25,53,7,91,202,0

以下是为了测试是为了验证TIMESTAMP的小数位存储算法：

<span>1</span> SQL<span>></span> <span>select</span> c2,<span>dump</span>(c2,<span>16</span>) c2_d16 <span>from</span> t;

C2                                       C2_D16
---------------------------------------- --------------------------------------------------------------------------------
12-FEB-10 01.24.52.234123211 PM          Typ=180 Len=11: 78,6e,2,c,e,19,35,d,f4,6f,cb
12-FEB-10 01.24.52.123456000 PM          Typ=180 Len=11: 78,6e,2,c,e,19,35,7,5b,ca,0

SQL<span>></span> <span>select</span> to_number(<span>'</span><span>0df46fcb</span><span>'</span>,<span>'</span><span>xxxxxxxx</span><span>'</span>) mydata1,to_number(<span>'</span><span>075bca00</span><span>'</span>,<span>'</span><span>xxxxxxxx</span><span>'</span>) mydata2 <span>from</span> dual;

 
   MYDATA1    MYDATA2
---------- ----------
 234123211  123456000

2、常见问题
2.1、如何取当前时间
sysdate--返回当前系统日期和时间，精确到秒
systimestamp--返回当前系统日期和时间，精确到毫秒
2.2、如何进行日期运算
日期型数据可以与数值加减得到新的日期，加减数值单位为天
sysdate+1--取明天的当前时间
sysdate-1/24--取当前时间的前一个小时

SQL<span>></span> <span>select</span> sysdate d1,sysdate<span>+</span><span>1</span> d2,sysdate<span>-</span><span>1</span><span>/</span><span>24</span> d3 <span>from</span> dual;

D1                       D2                       D3
------------------------ ------------------------ ------------------------
2010-5-13 下午 10:55:16  2010-5-14 下午 10:55:16  2010-5-13 下午 09:55:16

2.3、如何求两个日期的间隔时间
可以直接把两个日期相减，返回的单位为天，小时及分秒会换算成小数

SQL<span>></span> <span>select</span> date<span>'</span><span>2012-01-01</span><span>'</span><span>-</span>sysdate <span>from</span> dual;

 
DATE'2012-01-01'-SYSDATE
------------------------
        597.046030092593

2.4、如何将日期转字符
to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')
2.5、如何将字符转日期
to_date('2010-02-24 15:01:54','YYYY-MM-DD HH24:MI:SS')
to_timestamp('1999-12-01 11:00:00.123456','YYYY-MM-DD HH:MI:SS.FF6')

3、常用日期函数
3.1、TO_CHAR(DATE,FORMATSTR)--格式化日期成字符

SQL<span>></span> <span>select</span> to_char(sysdate,<span>'</span><span>YYYY-MM-DD HH24:MI:SS</span><span>'</span>) d1 <span>from</span> dual;

 
D1
------------------------
2010-05-13 22:56:38

TO_CHAR的其它用法示例

<span>1</span> SQL<span>></span> <span>SELECT</span> TO_CHAR(date <span>'</span><span>2010-02-12</span><span>'</span>, <span>'</span><span>D</span><span>'</span>) week_dayth,<span>--</span><span>周第几天(1-7)，星期天=1，星期一=2,星期二=3，星期三=4,星期四=5，星期五=6,星期六=7</span><span>2</span> TO_CHAR(date <span>'</span><span>2010-02-12</span><span>'</span>, <span>'</span><span>DD</span><span>'</span>) month_dayth,<span>--</span><span>月第几天</span><span>3</span> TO_CHAR(date <span>'</span><span>2010-02-12</span><span>'</span>, <span>'</span><span>DDD</span><span>'</span>) year_dayth,<span>--</span><span>年第几天</span><span>4</span> TO_CHAR(date <span>'</span><span>2010-02-12</span><span>'</span>, <span>'</span><span>DAY</span><span>'</span>) weekdayname,<span>--</span><span>英文星期名</span><span>5</span> _<span>CHAR</span>(date <span>'</span><span>2010-02-12</span><span>'</span>, <span>'</span><span>w</span><span>'</span>) month_weekth,<span>--</span><span>月第几周(0-4)</span><span>6</span> TO_CHAR(date <span>'</span><span>2010-02-12</span><span>'</span>, <span>'</span><span>ww</span><span>'</span>) year_weekth<span>--</span><span>年第几周(0-53)</span><span>7</span> <span>FROM</span> DUAL;

 

WEEK_DAYTH MONTH_DAYTH YEAR_DAYTH WEEKDAYNAME MONTH_WEEKTH YEAR_WEEKTH
---------- ----------- ---------- ----------- ------------ -----------
6          12          043        FRIDAY      2            07

3.2、TO_DATE(CHAR,FORMATSTR) --将字符转换成日期
to_date('2010-02-24 15:01:54','YYYY-MM-DD HH24:MI:SS')
格式备注：
HH表示12小时进制，HH24表示采用24小时进制，MM表示月份，MI表示分钟。

3.3、TRUNC(DATE)--返回DATE的日期部分，时间为0点0分0秒

SQL<span>></span> <span>select</span> sysdate d1,trunc(sysdate) d2 <span>from</span> dual;

 
D1                       D2
------------------------ ------------------------
2010-5-13 下午 10:59:18  2010-5-13

3.4、EXTRACT(DATA FROM DATEVALUE)--返回DATE的某一部份内容
 如果DATEVALUE为DATE类型，则DATA可以是(YEAR、MONTH、DAY)
 如果DATEVALUE为TIMESTAMP类型，则DATA可以是(YEAR、MONTH,DAY、HOUR、MINUTE、SECOND)

SQL<span>></span> <span>select</span> sysdate d1,EXTRACT(<span>YEAR</span> <span>FROM</span> sysdate) thisyear,EXTRACT(MINUTE <span>FROM</span> systimestamp) thism <span>from</span> dual;

 
D1                         THISYEAR      THISM
------------------------ ---------- ----------
2010-5-13 下午 11:05:06        2010          5
3.5、ADD_MONTHS(DATE,MONTHS) --在DATE增加月份得到新日期

ADD_MONTHS(sysdate,3) --当前日期加3个月
ADD_MONTHS是一个比较有意思的函数，它会自动处理大小月及闰月，如下所示

<span>1</span>   SQL<span>></span> <span>select</span> ADD_MONTHS(date <span>'</span><span>2010-2-12</span><span>'</span>, <span>1</span>),
<span>2</span>   ADD_MONTHS(date <span>'</span><span>2010-2-27</span><span>'</span>, <span>1</span>),
<span>3</span>   ADD_MONTHS(date <span>'</span><span>2010-2-28</span><span>'</span>, <span>1</span>),
<span>4</span>   ADD_MONTHS(date <span>'</span><span>2010-1-31</span><span>'</span>, <span>1</span>)
<span>5</span>   <span>from</span> dual
<span>6</span>   ;

 
ADD_MONTHS(DATE'2010-2-12',1) ADD_MONTHS(DATE'2010-2-27',1) ADD_MONTHS(DATE'2010-2-28',1) ADD_MONTHS(DATE'2010-1-31',1)
----------------------------- ----------------------------- ----------------------------- -----------------------------
2010-3-12                     2010-3-27                     2010-3-31                     2010-2-28

3.6、LAST_DAY(DATE)--返回日期所在月份的最后一天日期

SQL<span>></span> <span>select</span> LAST_DAY(date <span>'</span><span>2010-2-12</span><span>'</span>) <span>from</span> dual;

 
LAST_DAY(DATE'2010-2-12')
-------------------------
2010-2-28
 
3.7、NEXT_DAY(DATE,CHAR) --从给定日期开始返回下个CHAR指定星期的日期

SQL<span>></span> <span>SELECT</span> NEXT_DAY(date<span>'</span><span>2010-2-21</span><span>'</span>, <span>'</span><span>MONDAY</span><span>'</span>) NEXTDAY1,NEXT_DAY(date<span>'</span><span>2010-2-22</span><span>'</span>, <span>'</span><span>MONDAY</span><span>'</span>) NEXTDAY2 <span>FROM</span> DUAL;

 
NEXTDAY1    NEXTDAY2
----------- -----------
2010-2-22   2010-3-1

TO_YMINTERVAL(CHAR)--返回[年-月]格式构成的时间间隔，一般用于日期加减运算

3.8、TO_DSINTERVAL(CHAR)--返回[天 时:分:秒]格式构成的时间间隔，一般用于日期加减运算

SQL<span>></span> <span>select</span> date<span>'</span><span>2010-2-12</span><span>'</span><span>+</span>TO_YMINTERVAL(<span>'</span><span>01-02</span><span>'</span>) newdate <span>from</span> dual;

 
NEWDATE
------------------------------
2011-4-12

3.9、NUMTOYMINTERVAL(N,CHAR) --返回CHAR中指定单位的时间间隔数值，一般用于日期加减运算
char可以为YEAR,MONTH

<span>1</span> SQL<span>></span> <span>select</span> date <span>'</span><span>2010-2-12</span><span>'</span> <span>+</span> NUMTOYMINTERVAL(<span>1</span>, <span>'</span><span>year</span><span>'</span>) newdate1,
<span>2</span> date <span>'</span><span>2010-2-12</span><span>'</span> <span>+</span> NUMTOYMINTERVAL(<span>1</span>, <span>'</span><span>month</span><span>'</span>) newdate2
<span>3</span> <span>from</span> dual;

 
NEWDATE1    NEWDATE2
----------- -----------
2011-2-12   2010-3-12

3.10、NUMTODSINTERVAL(N,CHAR) --返回CHAR中指定单位的时间间隔数值，一般用于日期加减运算
char可以为DAY,HOUR,MINUTE,SECOND

<span>1</span> SQL<span>></span> <span>select</span> date <span>'</span><span>2010-2-12</span><span>'</span> <span>+</span> NUMTODSINTERVAL(<span>1</span>, <span>'</span><span>DAY</span><span>'</span>) newdate1,
<span>2</span> date <span>'</span><span>2010-2-12</span><span>'</span> <span>+</span> NUMTODSINTERVAL(<span>1</span>, <span>'</span><span>HOUR</span><span>'</span>) newdate2
<span>3</span> <span>from</span> dual;

 
NEWDATE1    NEWDATE2
----------- ------------------------
2010-2-13   2010-2-12 上午 01:00:00

http://www.cnblogs.com/chuncn/archive/2009/01/29/1381281.html

http://wenku.baidu.com/view/65b46e1810a6f524ccbf855b.html

http://wenku.baidu.com/view/c41654d084254b35eefd341c.html

oracle date 数据类型  谷歌