Oracle 日期计算 与 ROUND TRUNC 函数日期计算

 

SQL*Plus: Release 10.2.0.1.0 - Production on D??ú?t 9?? 4 10:11:57 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


á??óμ?: 
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

mdev@mdev-192.168.16.35> -- 当前时间
mdev@mdev-192.168.16.35> 
mdev@mdev-192.168.16.35> SELECT SYSDATE FROM DUAL;

SYSDATE
--------------
04-9?? -12

mdev@mdev-192.168.16.35> 
mdev@mdev-192.168.16.35> -- 设置相近时间
mdev@mdev-192.168.16.35> 
mdev@mdev-192.168.16.35> SELECT TO_DATE('2012/09/04 08:00:00', 'YYYY/MM/DD HH24:MI:SS') FROM DUAL;

TO_DATE('2012/
--------------
04-9?? -12

mdev@mdev-192.168.16.35> 
mdev@mdev-192.168.16.35> -- 两个时间相减: 差值——天数
mdev@mdev-192.168.16.35> 
mdev@mdev-192.168.16.35> SELECT   TO_DATE('2012/09/04 08:00:00', 'YYYY/MM/DD HH24:MI:SS')
  2         - TO_DATE('2012/09/03 08:00:00', 'YYYY/MM/DD HH24:MI:SS')
  3    FROM DUAL;

TO_DATE('2012/09/0408:00:00','YYYY/MM/DDHH24:MI:SS')-TO_DATE('2012/09/0308:00:00','YYYY/MM/DDHH24:MI
----------------------------------------------------------------------------------------------------
                                                                                                        1

mdev@mdev-192.168.16.35> 
mdev@mdev-192.168.16.35> SELECT SYSDATE - TO_DATE('2012/09/04 08:00:00', 'YYYY/MM/DD HH24:MI:SS')
  2    FROM DUAL;

SYSDATE-TO_DATE('2012/09/0408:00:00','YYYY/MM/DDHH24:MI:SS')
------------------------------------------------------------
                                                  .072326389

mdev@mdev-192.168.16.35> 
mdev@mdev-192.168.16.35> -- 两个时间相减：差值——小时数
mdev@mdev-192.168.16.35> 
mdev@mdev-192.168.16.35> SELECT   (SYSDATE - TO_DATE('2012/09/04 08:00:00', 'YYYY/MM/DD HH24:MI:SS')
)
  2         * 24
  3    FROM DUAL;

(SYSDATE-TO_DATE('2012/09/0408:00:00','YYYY/MM/DDHH24:MI:SS'))*24
-----------------------------------------------------------------
                                                       1.73583333

mdev@mdev-192.168.16.35> 
mdev@mdev-192.168.16.35> -- 两个时间相减：差值——分钟数
mdev@mdev-192.168.16.35> 
mdev@mdev-192.168.16.35> SELECT   (SYSDATE - TO_DATE('2012/09/04 08:00:00', 'YYYY/MM/DD HH24:MI:SS')
)
  2         * 24
  3         * 60
  4    FROM DUAL;

(SYSDATE-TO_DATE('2012/09/0408:00:00','YYYY/MM/DDHH24:MI:SS'))*24*60
--------------------------------------------------------------------
                                                              104.15

mdev@mdev-192.168.16.35> 
mdev@mdev-192.168.16.35> -- 两个时间相减：差值——秒数
mdev@mdev-192.168.16.35> 
mdev@mdev-192.168.16.35> SELECT   (SYSDATE - TO_DATE('2012/09/04 08:00:00', 'YYYY/MM/DD HH24:MI:SS')
)
  2         * 24
  3         * 60
  4         * 60
  5    FROM DUAL;

(SYSDATE-TO_DATE('2012/09/0408:00:00','YYYY/MM/DDHH24:MI:SS'))*24*60*60
-----------------------------------------------------------------------
                                                                   6249

mdev@mdev-192.168.16.35> 
mdev@mdev-192.168.16.35> -- ROUND函数将该天的时间设置为零点，如果其时间为下午之后，设置为次日的零点
mdev@mdev-192.168.16.35> 
mdev@mdev-192.168.16.35> SELECT   TO_DATE('2012/09/04 00:00:00', 'YYYY/MM/DD HH24:MI:SS')
  2         - ROUND(TO_DATE('2012/09/04 11:59:59', 'YYYY/MM/DD HH24:MI:SS'))
  3    FROM DUAL;

TO_DATE('2012/09/0400:00:00','YYYY/MM/DDHH24:MI:SS')-ROUND(TO_DATE('2012/09/0411:59:59','YYYY/MM/DDH
----------------------------------------------------------------------------------------------------
                                                                                                               0

mdev@mdev-192.168.16.35> 
mdev@mdev-192.168.16.35> SELECT   TO_DATE('2012/09/04 00:00:00', 'YYYY/MM/DD HH24:MI:SS')
  2         - ROUND(TO_DATE('2012/09/04 12:00:00', 'YYYY/MM/DD HH24:MI:SS'))
  3    FROM DUAL;

TO_DATE('2012/09/0400:00:00','YYYY/MM/DDHH24:MI:SS')-ROUND(TO_DATE('2012/09/0412:00:00','YYYY/MM/DDH
----------------------------------------------------------------------------------------------------
                                                                                                              -1

mdev@mdev-192.168.16.35> 
mdev@mdev-192.168.16.35> SELECT   TO_DATE('2012/09/04 00:00:00', 'YYYY/MM/DD HH24:MI:SS')
  2         - ROUND(TO_DATE('2012/09/05 11:59:59', 'YYYY/MM/DD HH24:MI:SS'))
  3    FROM DUAL;

TO_DATE('2012/09/0400:00:00','YYYY/MM/DDHH24:MI:SS')-ROUND(TO_DATE('2012/09/0511:59:59','YYYY/MM/DDH
----------------------------------------------------------------------------------------------------
                                                                                                              -1

mdev@mdev-192.168.16.35> 
mdev@mdev-192.168.16.35> -- TRUNC函数的功能与之类似，只不过将任何时间，包括午夜后一秒也设置为零点
mdev@mdev-192.168.16.35> 
mdev@mdev-192.168.16.35> SELECT   TO_DATE('2012/09/04 00:00:00', 'YYYY/MM/DD HH24:MI:SS')
  2         - TRUNC(TO_DATE('2012/09/04 11:59:59', 'YYYY/MM/DD HH24:MI:SS'))
  3    FROM DUAL;

TO_DATE('2012/09/0400:00:00','YYYY/MM/DDHH24:MI:SS')-TRUNC(TO_DATE('2012/09/0411:59:59','YYYY/MM/DDH
----------------------------------------------------------------------------------------------------
                                                                                                               0

mdev@mdev-192.168.16.35> 
mdev@mdev-192.168.16.35> SELECT   TO_DATE('2012/09/04 00:00:00', 'YYYY/MM/DD HH24:MI:SS')
  2         - TRUNC(TO_DATE('2012/09/04 12:00:00', 'YYYY/MM/DD HH24:MI:SS'))
  3    FROM DUAL;

TO_DATE('2012/09/0400:00:00','YYYY/MM/DDHH24:MI:SS')-TRUNC(TO_DATE('2012/09/0412:00:00','YYYY/MM/DDH
----------------------------------------------------------------------------------------------------
                                                                                                               0

mdev@mdev-192.168.16.35> 
mdev@mdev-192.168.16.35> SELECT   TO_DATE('2012/09/04 00:00:00', 'YYYY/MM/DD HH24:MI:SS')
  2         - TRUNC(TO_DATE('2012/09/05 11:59:59', 'YYYY/MM/DD HH24:MI:SS'))
  3    FROM DUAL;

TO_DATE('2012/09/0400:00:00','YYYY/MM/DDHH24:MI:SS')-TRUNC(TO_DATE('2012/09/0511:59:59','YYYY/MM/DDH
----------------------------------------------------------------------------------------------------
                                                                                                              -1

mdev@mdev-192.168.16.35>