首页 / 数据库 / MySQL / Oracle 时间类型及函数处理

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


 注：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。Win7 64X 下安装 Oracle 10gOracle 查询执行顺序相关资讯      Oracle数据库  Oracle入门教程  oracle数据库教程 

Oracle数据库全球化  （03月01日） Oracle数据库日期过滤方法性能比较  （02/02/2015 13:20:26） Oracle数据库安装中端口被占用问题  （10/29/2014 07:42:24）

在CentOS 6.6上搭建C++运行环境并  （10/10/2015 19:44:40） Oracle数据库无法使用localhost和  （11/14/2014 16:39:10） 使用SQLT来构建Oracle测试用例  （08/28/2014 06:17:41）

本文评论 查看全部评论 （0）

表情： 姓名： 匿名字数 <div id="footbar"> 版权所有©石家庄振强科技有限公司2024 <a href="https://beian.miit.gov.cn" target="_blank">冀ICP备08103738号-5</a> <a href="/storage/sitemap.xml">网站地图</a> </div> </div> <script type="text/javascript" src="//cpro.baidustatic.com/cpro/ui/cm.js"></script> <script> var _mtj = _mtj || []; (function () { var mtj = document.createElement("script"); mtj.src = "https://node12.aizhantj.com:21233/tjjs/?k=p2tceukth5c"; var s = document.getElementsByTagName("script")[0]; s.parentNode.insertBefore(mtj, s); })(); </script> </body> </html>