(轉)PLS_INTEGER型別與timestamp型別、date、及時間函式
今天看到了這個 PLS_INTEGER，覺得生疏，網上搜了下，原來這個型別的好處多多啊。因此將這些轉了過來，收藏了。
PLS_INTEGER可以儲存一個有符號的整形值，其精度範圍和BINARY_INTEGER一樣，是：-2^31~2^31。
PLS_INTEGER和NUMBER比較起來，其優點是：
1).佔有較少的儲存空間；
2).可以直接進行算術運算（在NUMBER上不能直接進行算術運算，如果要計算，NUMBER必須先被轉換成二進位制）。所以在進行算術的時候PLS_INTEGER比NUMBER和BINARY_INTEGER快一些。
PLS_INTEGER和BINARY_INTEGER區別：
PLS_INTEGER進行的運算發生溢位的時候，會觸發異常。但是當BINARY_INTEGER運算發生溢位時，如果可以指派給一個NUMBER變數(沒有超出NUMBER的精度範圍)的話就不會觸發異常。
二、date型別使用
而date型別相減只得到天數
SQL> SELECT sysdate-(sysdate-1) FROM dual;
SYSDATE-(SYSDATE-1)
-------------------
1
做個試驗：
create table t2 (d1 date,d2 date) ;--建立一個表
INSERT INTO T2 VALUES (SYSDATE, SYSDATE + 1.1234); --插入一條資料
SQL> SELECT D1, D2, D2 - D1, CAST(D2 AS TIMESTAMP) - CAST(D1 AS TIMESTAMP) FROM T2;
D1 D2 D2-D1 CAST(D2ASTIMESTAMP)-CAST(D1AST
----------- ----------- ---------- ---------------------------------------
2010-12-2 1 2010-12-3 1 1.12340277 +000000001 02:57:42.000000
date型別相減轉換為天、時分秒
SQL> SELECT TO_NUMBER(SUBSTR(D21, 1, INSTR(D21, ' '))) D,
2 SUBSTR(D21, INSTR(D21, ' ') + 1, 2) H,
3 SUBSTR(D21, INSTR(D21, ' ') + 4, 2) M,
4 SUBSTR(D21, INSTR(D21, ' ') + 7, 2) S
5 FROM (SELECT CAST(D2 AS TIMESTAMP) - CAST(D1 AS TIMESTAMP) D21
6 FROM T2) ;
D H M S
---------- ---- ---- ----
1 02 57 42
--兩個date日期相減
select dt2 - dt1,
months_between(dt2, dt1) months_btwn,
numtodsinterval(dt2 - dt1, 'day') days,
numtoyminterval(months_between(dt2, dt1), 'month') years_months
from (select to_date('29-2-2000 01:02:03',
'dd-mm-yyyy hh24:mi:ss') dt1,
to_date('15-3-2001 11:22:33',
'dd-mm-yyyy hh24:mi:ss') dt2
from dual);
--轉換為年，天時分秒
select numtoyminterval(months_between(dt2, dt1), 'month') years_months,
numtodsinterval(dt2 -
add_months(dt1, trunc(months_between(dt2, dt1))),
'day') days_hours
from (select to_date('29-2-2000 01:02:03', 'dd-mm-yyyy hh24:mi:ss') dt1,
to_date('15-3-2001 11:22:33', 'dd-mm-yyyy hh24:mi:ss') dt2
from dual);
三、timestamp型別
timestamp 語法：timestamp（n）
--當n=0時，與date長度一樣
create table t (dt date,ts timestamp(0));
insert into t values (sysdate,systimestamp);
SQL> select dump(dt,10) dump,dump(ts,10) dump from t;
DUMP DUMP
------------------------------------------ ---------------------------------------
Typ=12 Len=7: 120,110,12,4,14,10,10 Typ=180 Len=7: 120,110,12,4,14,10,10
--n為9
create table t (dt date,ts timestamp(9));
insert into t values (sysdate,systimestamp);
SQL> select dump(dt,10) dump,dump(ts,10) dump from t;
DUMP DUMP
----------------------------------- --------------------------------------------------------
Typ=12 Len=7: 120,110,12,4,14,7,41 Typ=180 Len=11: 120,110,12,4,14,7,41,11,37,100,192
SQL> select * from t;
DT TS
----------- -----------------------------------
2010-12-4 1 04-12月-10 01.54.09.625000000 下午 --n為9即秒後面保留的位數（625000000）
select dump(ts,16) dump from t;
Typ=180 Len=11: 78,6e,c,4,e,37,a,25,40,be,40
SQL> select to_number('2540be40','xxxxxxxx') from dual;
TO_NUMBER('2540BE40','XXXXXXXX'）--將後四個位元組轉換為十進位制
------------------------------
625000000 --後面四個位元組就是秒小數點後面的數字
--timestamp 型別包括了所有DATE資料型別的年月日時分秒的資訊，而且包括了小數秒的資訊
SQL> SELECT sysdate,systimestamp FROM dual;
SYSDATE SYSTIMESTAMP
----------- ---------------------------------------
2011-3-4 15 04-3月 -11 03.18.16.625000 下午 +08:00
timestamp直接進行加減時會隱式的轉為date型
SQL> select systimestamp ts,systimestamp+1 dt from dual;
TS DT
-------------------------------------------- -----------
04-12月-10 10.40.00.265000 上午 +08:00 2010-12-5 1
解決隱式轉換問題（仍保持timestamp型別）
SQL> select systimestamp ts,systimestamp+numtodsinterval(1,'day') dt from dual;
TS DT
-------------------------------------------- ----------------------------------------------
04-12月-10 10.42.46.515000 上午 +08:00 05-12月-10 10.42.46.515000000 上午 +08:00
兩個timestamp相減
SQL> select dt2 - dt1
2 from (select to_timestamp('29-2-2000 01:02:03.122000',
3 'dd-mm-yyyy hh24:mi:ss.ff') dt1,
4 to_timestamp('15-3-2001 11:22:33.000000',
5 'dd-mm-yyyy hh24:mi:ss.ff') dt2
6 from dual);
DT2-DT1
---------------------------------------
+000000380 10:20:29.878000000
將其轉換為具體的年、天時分秒
SQL> select numtoyminterval(months_between(dt2, dt1), 'month') years_months,
2 dt2 - add_months(dt1, trunc(months_between(dt2, dt1))) days_hours
3 from (select to_timestamp('29-2-2000 01:02:03.122000',
4 'dd-mm-yyyy hh24:mi:ss.ff') dt1,
5 to_timestamp('15-3-2001 11:22:33.000000',
6 'dd-mm-yyyy hh24:mi:ss.ff') dt2
7 from dual);
YEARS_MONTHS DAYS_HOURS
--------------------------------------- ---------------------------------------
+000000001-00 +000000015 10:20:30.000000000
關於numtodsinterval 參考文獻########################################
Oracle/PLSQL: NumToDSInterval Function
In Oracle/PLSQL, the numtodsinterval function converts a number to an INTERVAL DAY TO SECOND literal.
The syntax for the numtodsinterval function is:
numtodsinterval( number, expression )
number is the number to convert to an interval.
expression is the unit. It must be one of the following values: DAY, HOUR, MINUTE, or SECOND.
Applies To:
- Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g
For example:
numtodsinterval(150, 'DAY') would return '+000000150' numtodsinterval(1500, 'HOUR') would return '+000000062' numtodsinterval(15000, 'MINUTE') would return '+000000010' numtodsinterval(150000, 'SECOND') would return '+000000001'
###############################################
注：to_char函式支援date和timestamp，但是trunc卻不支援TIMESTAMP資料型別
SQL> select trunc(to_date('20100908','yyyymmdd') - to_date('20100907','yyyymmdd')) m from dual;
M
----------
1
--兩個相減的timestamp型別不能用trunc
SQL> select trunc(to_timestamp('20100908','yyyymmdd') - to_timestamp('20100907','yyyymmdd')) m from dual;
select trunc(to_timestamp('20100908','yyyymmdd') - to_timestamp('20100907','yyyymmdd')) m from dual
ORA-00932: 資料型別不一致: 應為 NUMBER, 但卻獲得 INTERVAL DAY TO SECOND
to_char的使用：
SQL> SELECT sysdate,systimestamp,to_char(systimestamp,'yyyymmdd hh24:mi:ssxff3') FROM dual;
SYSDATE SYSTIMESTAMP TO_CHAR(SYSTIMESTAMP,'YYYYMMDD'
----------- -------------------------------------------------------- ------------------------------
2010-12-2 1 02-12月-10 02.06.34.046000 下午 +08:00 20101202 14:06:34.046
對於 timestamp型別的資料直接相減可以精確到秒以後
SQL> select systimestamp-systimestamp from dual;
SYSTIMESTAMP-SYSTIMESTAMP
---------------------------------------
+000000000 00:00:00.000000
四、interval year to month 型別的使用
interval year to month 可以用numtoyminterval或to_yminterval函式替代
SQL> select numtoyminterval(5,'year') + numtoyminterval(2,'month') from dual;
NUMTOYMINTERVAL(5,'YEAR')+NUMT
---------------------------------------
+000000005-02
SQL> select numtoyminterval(5*12+2,'month') from dual;
NUMTOYMINTERVAL(5*12+2,'MONTH'
---------------------------------------
+000000005-02
SQL> select to_yminterval('5-2') from dual;
TO_YMINTERVAL('5-2')
---------------------------------------
+000000005-02
SQL> select interval '5-2' year to month from dual;
INTERVAL'5-2'YEARTOMONTH
---------------------------------------
+05-02
五、interval day to second 型別的使用
interval day to second 可以用numtodsinterval或to_dsinterval替換使用
SQL> select numtodsinterval(10, 'day') + numtodsinterval(2, 'hour') +
2 numtodsinterval(3, 'minute') + numtodsinterval(2.3312, 'second')
3 from dual;
NUMTODSINTERVAL(10,'DAY')+NUMT
---------------------------------------
+000000010 02:03:02.331200000
SQL> select numtodsinterval(10*24*60*60+2*3600+3*60+2.3312, 'second') from dual;
NUMTODSINTERVAL(10*24*60*60+2*
---------------------------------------
+000000010 02:03:02.331200000
SQL> select to_dsinterval('10 02:03:02.3312') from dual;
TO_DSINTERVAL('1002:03:02.3312'
---------------------------------------
+000000010 02:03:02.331200000
SQL> select interval '10 02:03:02.3312' day to second from dual;
INTERVAL'1002:03:02.3312'DAYTO
---------------------------------------
+10 02:03:02.331200
六、時間函式
ADD_MONTHS 函式
ADD_MONTHS函式將一個日期上加上一指定的月份數，所以，日期中的日將是不變的。然而，如果開始日期是某月的最後一天，那麼，結果將會調整以使
返回值仍對應新的一月的最後一天。如果，結果月份的天數比開始月份的天數少，那麼，也會向回撥整以適應有效日期
select ADD_MONTHS(TO_DATE('15-11-1961','dd-mm-yyyy'),1) from dual; --1961-12-15
select ADD_MONTHS(TO_DATE('30-11-1961','dd-mm-yyyy'),1) from dual; --1961-12-31
select ADD_MONTHS(TO_DATE('31-1-1999','dd-mm-yyyy'),1) from dual;--1999-2-28
MONTHS_BETWEEN 函式
MONTHS_BETWEEN (x, y)用於計算x和y之間有幾個月。如果x在日曆中比y早，那麼MONTHS_BETWEEN()就返回一個負數。
當x 和 y 之間的月份之差不是整月的時候，可以採用小數表示
NUMTODSINTERVAL()函式
NUMTODSINTERVAL(x, interval_unit)用於將以interval_unit指定的值為單位的數字x轉換為一個INTERVAL DAY TO SECOND型別，
interval_unit引數可以設定為DAY、 HOUR、MINUTE或SECOND。
NUMTOYMINTERVAL()函式
NUMTOYMINTERVAL(x, interval_unit)用於將以interval_unit指定的值為單位的數字x轉換為一個INTERVAL YEAR TO MONTH型別，
interval_unit引數可以設定為YEAR或MONTH。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12272958/viewspace-706415/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 將timestamp型別轉換為date型別型別
- oracle timestamp轉換date及date型別相減Oracle型別
- date和timestamp型別之間的轉換型別
- oracle中date資料型別與timestamp資料型別的轉換Oracle資料型別
- 一個簡單函式—Number型別轉換Date型別函式型別
- date、timestamp欄位型別型別
- ORACLE DATE型別和TIMESTAMP型別DUMP換算Oracle型別
- pls_integer型別型別
- oracle資料型別date和timestamp的轉化Oracle資料型別
- Java中將時間戳轉化為Date型別Java時間戳型別
- 字元型別轉換成時間型別字元型別
- PostgreSQL：時間——型別、運算子和函式SQL型別函式
- 使用pls_integer型別型別
- TIMESTAMP型別的時區型別
- oracle將表中date資料型別修改為timestamp資料型別Oracle資料型別
- PostgreSQL：字元——型別及函式SQL字元型別函式
- date型別型別
- ORACLE DATE和TIMESTAMP資料型別的比較(一) (轉)Oracle資料型別
- ORACLE DATE和TIMESTAMP資料型別的比較(二) (轉)Oracle資料型別
- 字串和Date型別之間的轉換字串型別
- Date型別和Regex型別型別
- Date型別使用Calendar進行時間運算型別
- sqlserver 時間型別 轉化SQLServer型別
- Oracle timestamp型別轉換date格式指令碼（精確到秒）Oracle型別指令碼
- 時間型別型別
- SQL SERVER 日期和時間資料型別及函式 (Transact-SQL)SQLServer資料型別函式
- 時間型別及格式轉換型別
- Mysql日期（DATE, TIME, DATETIME, TIMESTAMP）型別的比較MySql型別
- 各種時間型別的轉換及比較型別
- MySql與Java的時間型別MySqlJava型別
- TypeScript 函式型別TypeScript函式型別
- 資料庫中Date型別的計算 DATEDIFF() 函式資料庫型別函式
- Java資料型別及型別轉換Java資料型別
- 輸入TIMESTAMP型別型別
- Timestamp型別淺析型別
- Sybase的timestamp型別型別
- Golang 中函式作為值與型別(轉載)Golang函式型別
- MySQL資料庫中的timestamp型別與時區MySql資料庫型別