Home > Database > Mysql Tutorial > body text

Oracle 时间类型及函数处理

WBOY
Release: 2016-06-07 17:06:08
Original
935 people have browsed it

一、date类型使用 而date类型相减只得到天数SQLgt; SELECT sysdate-(sysdate-1) FROM dual;SYSDATE-(SYSDATE-1)--------------

一、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


三、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。

linux

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template