MySQL之 INTERVAL

转自：http://blog.csdn.net/holly2008/article/details/23629351

一、INTERVAL YEAR TO MONTH / INTERVAL DAY TO SECOND
语法:
　　INTERVAL 'integer [- integer]' {YEAR | MONTH} [(precision)][TO {YEAR | MONTH}]
       这是求精确的时间间隔,把一段间隔时间明细化的一种方法

Interval year(precision) to month  
       precision是这个时限的年部分所要求的最大位数。 默认为2，范围为0~9

Interval day(d_precision) to second(s_precision)  
       d_precision是这个时限的天部分所要求的最大位数,默认为2，范围也是0~9
       s_precision是这个时限的秒部分所要求的小数点右边的位数，默认为6，范围是0~9

例如：
     问你进入公司多少年了? 这个很容易答出来
     问你进入公司多少天了? 这个就比较麻烦了，因为月份不同，天数不同，还涉及到闰年
     而这种类型就能给取出非常准确的时间间隔

例如：计算SCOTT至今为止在公司多久了.
year to month 返回27年0个月
day to second 返回9857天11小时49分22秒....

     SQL> SELECT (sysdate - hiredate) YEAR TO MONTH time FROM emp where ename='SCOTT';

     TIME
     ------------
     +27-00

     SQL> SELECT (sysdate - hiredate) DAY(9) TO SECOND time FROM emp where ename='SCOTT';

     TIME
     -----------------------------
     +000009857 11:49:22.000000

    
     SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

     Session altered.

     SQL> select sysdate + INTERVAL '1-2' YEAR TO MONTH from dual;

     SYSDATE+INTERVAL'1-
     -------------------
     2015-06-14 11:42:24

     表示: 当前系统时间  +  1年2个月

     INTERVAL '123' YEAR(3)
     表示: 123年0个月, "YEAR(3)" 表示年的精度为3, "123"刚好为3为有效数值, 如果该处YEAR(n), n<3就会出错, 注意默认是2.

     SQL> select sysdate + INTERVAL '123' YEAR from dual;
     select sysdate + INTERVAL '123' YEAR from dual
                               *
     ERROR at line 1:
     ORA-01873: the leading precision of the interval is too small

     INTERVAL '300' MONTH(3)
     表示: 300个月, 注意该处MONTH的精度是3.

     INTERVAL '4' YEAR
     表示: 4年, 同 INTERVAL '4-0' YEAR TO MONTH 是一样的

     INTERVAL '20' MONTH
     表示: 20个月, 同 INTERVAL '1-8' YEAR TO MONTH 是一样

     INTERVAL '5-3' YEAR TO MONTH + INTERVAL '20' MONTH =  INTERVAL '6-11' YEAR TO MONTH
     表示: 5年3个月 + 20个月 = 6年11个月

二、NUMTOYMINTERVAL /NUMTODSINTERVAL

　　NUMTODSINTERVAL(n, 'interval_unit')
　　将n转换成interval_unit所指定的值, interval_unit可以为: DAY, HOUR, MINUTE, SECOND
　　注意:该函数不可以转换成YEAR和MONTH的.

　　NUMTOYMINTERVAL(n, 'interval_unit')
　　interval_unit可以为: YEAR, MONTH

       SQL> select sysdate,sysdate+numtodsinterval(3,'hour') as res from dual;

       SYSDATE                             RES
       ---------------------------           -------------------------------
       2014-04-14 13:37:09           2014-04-14 16:37:09

　　SQL> select numtodsinterval(100,'DAY') from dual;

　　NUMTODSINTERVAL(100,'DAY')
　　---------------------------------------------------------------------------
　　+000000100 00:00:00.000000000

　　SQL> select numtodsinterval(100,'SECOND') from dual

　　NUMTODSINTERVAL(100,'SECOND')
　　---------------------------------------------------------------------------
　　+000000000 00:01:40.000000000

　　SQL> select numtodsinterval(100,'MINUTE') from dual

　　NUMTODSINTERVAL(100,'MINUTE')
　　--------------------------------------------------------------------------
　　+000000000 01:40:00.000000000

　　SQL> select numtodsinterval(100,'HOUR') from dual

　　NUMTODSINTERVAL(100,'HOUR')
　　---------------------------------------------------------------------------
　　+000000004 04:00:00.000000000

　　SQL> select numtodsinterval(100,'YEAR') from dual
　　select numtodsinterval(100,'YEAR') from dual
　　*
　　ERROR at line 1:
　　ORA-01760: illegal argument for function

　　SQL> select numtoyminterval(100,'year') from dual;

　　NUMTOYMINTERVAL(100,'YEAR')
　　---------------------------------------------------------------------------
　　+000000100-00

　　SQL> select numtoyminterval(100,'month') from dual

　　NUMTOYMINTERVAL(100,'MONTH')
　　---------------------------------------------------------------------------
　　+000000008-04

　　时间的计算:

　　SQL> select to_date('2014-2-12','yyyy-mm-dd') - to_date('2014-2-01','yyyy-mm-dd') TIME from dual;

　　TIME
　　--------------
　　11

　　-- 可以相减的结果为天.


　　SQL>  select to_date('2013-01-12','yyyy-mm-dd') - to_date('2014-2-01','yyyy-mm-dd') time from dual;

　　TIME
　　-------------------
　　-385

　　-- 也可以为负数的

　　下面看看INTERVAL YEAR TO MONTH怎么用.
　　SQL> create table test(a date, b date, c interval year(9) to month);

　　Table created.

　　SQL> desc test

　　     Name             Null?    Type
    -----------------   -------- ---------------------------
     A                              DATE
     B                              DATE
     C                              INTERVAL YEAR(9) TO MONTH

　　SQL> insert into test values(to_date('2014-2-12','yyyy-mm-dd'),to_date('2014-2-01','yyyy-mm-dd'), null);

　　1 row created.


       SQL>  insert into test values(to_date('2013-01-12','yyyy-mm-dd'),to_date('2014-2-01','yyyy-mm-dd'), null);

      1 row created.

      SQL> update test set c = numtoyminterval(a-b, 'year');

      2 rows updated.

      SQL> select * from test;

    A                   B                   C
   ------------------- ------------------- ---------------
   2014-02-12 00:00:00 2014-02-01 00:00:00 +000000011-00
   2013-01-12 00:00:00 2014-02-01 00:00:00 -000000385-0