oracle数据类型详解---⽇期型（转载）
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类型内部存储验证
1create table T
2 (
3 C1 DATE,
4 C2 TIMESTAMP(9)
5 );
6
7insert into t(c1,c2) values(date'2010-2-12',timestamp'2010-2-12 13:24:52.234123211');
8insert into t(c1,c2) values(
9 to_date('2010-2-12 10:20:30','YYYY-MM-DD HH24:MI:SS'),
10 to_timestamp('2010-2-12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6')
11 );
12
13 SQL>select c1,dump(c1) c1_d,c2,dump(c2) c2_d from 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的⼩数位存储算法：
1 SQL>select c2,dump(c2,16) c2_d16 from 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>select to_number('0df46fcb','xxxxxxxx') mydata1,to_number('075bca00','xxxxxxxx') mydata2 from dual;
MYDATA1 MYDATA2
---------- ----------
234123211 123456000
2、常见问题
2.1、如何取当前时间
sysdate--返回当前系统⽇期和时间，精确到秒
systimestamp--返回当前系统⽇期和时间，精确到毫秒
2.2、如何进⾏⽇期运算
⽇期型数据可以与数值加减得到新的⽇期，加减数值单位为天
sysdate+1--取明天的当前时间
sysdate-1/24--取当前时间的前⼀个⼩时
SQL>select sysdate d1,sysdate+1 d2,sysdate-1/24 d3 from 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>select date'2012-01-01'-sysdate from 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>select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') d1 from dual;
D1
-
-----------------------
2010-05-13 22:56:38
TO_CHAR的其它⽤法⽰例
1 SQL>SELECT TO_CHAR(date '2010-02-12', 'D') week_dayth,--周第⼏天(1-7)，星期天=1，星期⼀=2，星期⼆=3，星期三=4，星期四=5，星期五=6，星期六=7
2 TO_CHAR(date '2010-02-12', 'DD') month_dayth,--⽉第⼏天
3 TO_CHAR(date '2010-02-12', 'DDD') year_dayth,--年第⼏天
4 TO_CHAR(date '2010-02-12', 'DAY') weekdayname,--英⽂星期名
5 _CHAR(date '2010-02-12', 'w') month_weekth,--⽉第⼏周(0-4)
6 TO_CHAR(date '2010-02-12', 'ww') year_weekth--年第⼏周(0-53)
7FROM 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>select sysdate d1,trunc(sysdate) d2 from 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>select sysdate d1,EXTRACT(YEAR FROM sysdate) thisyear,EXTRACT(MINUTE FROM systimestamp) thism from 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是⼀个⽐较有意思的函数，它会⾃动处理⼤⼩⽉及闰⽉，如下所⽰
1 SQL>select ADD_MONTHS(date '2010-2-12', 1),
2 ADD_MONTHS(date '2010-2-27', 1),
3 ADD_MONTHS(date '2010-2-28', 1),
4 ADD_MONTHS(date '2010-1-31', 1)
5from dual
6 ;
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>select LAST_DAY(date '2010-2-12') from dual;
LAST_DAY(DATE'2010-2-12')
-------------------------
2010-2-28
3.7、NEXT_DAY(DATE,CHAR) --从给定⽇期开始返回下个CHAR指定星期的⽇期
SQL>SELECT NEXT_DAY(date'2010-2-21', 'MONDAY') NEXTDAY1,NEXT_DAY(date'2010-2-22', 'MONDAY') NEXTDAY2 FROM DUAL;
NEXTDAY1 NEXTDAY2
----------- -----------
2010-2-22 2010-3-1
TO_YMINTERVAL(CHAR)--返回[年-⽉]格式构成的时间间隔，⼀般⽤于⽇期加减运算
3.8、TO_DSINTERVAL(CHAR)--返回[天时：分：秒]格式构成的时间间隔，⼀般⽤于⽇期加减运算
SQL>select date'2010-2-12'+TO_YMINTERVAL('01-02') newdate from dual;
NEWDATE
------------------------------
2011-4-12
3.9、NUMTOYMINTERVAL(N,CHAR) --返回CHAR中指定单位的时间间隔数值，⼀般⽤于⽇期加减运算
char可以为YEAR,MONTH
1 SQL>select date '2010-2-12'+ NUMTOYMINTERVAL(1, 'year') newdate1,
2 date '2010-2-12'+ NUMTOYMINTERVAL(1, 'month') newdate2
3from dual;
NEWDATE1 NEWDATE2
----------- -----------
2011-2-12 2010-3-12
3.10、NUMTODSINTERVAL(N,CHAR) --返回CHAR中指定单位的时间间隔数值，⼀般⽤于⽇期加减运算char可以为DAY,HOUR,MINUTE,SECOND
1 SQL>select date '2010-2-12'+ NUMTODSINTERVAL(1, 'DAY') newdate1,
2 date '2010-2-12'+ NUMTODSINTERVAL(1, 'HOUR') newdate2
3from dual;
NEWDATE1 NEWDATE2oracle 时间转换
----------- ------------------------
2010-2-13 2010-2-12 上午 01:00:00
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论