当前位置:数据库 > Oracle >>

Oracle中与时间有关的计算

Oracle中与时间有关的计算
 
[sql] 
# sysdate+1/24        - 加1小时  
# sysdate+1/24/60     - 加1分钟  
# sysdate+1/24/60/60  - 加1秒钟  
#   
SQL> alter session set nls_date_format='yyyy-MM-dd hh24:mi:ss';  
  
Session altered.  
  
SQL> select sysdate, sysdate+1/24, sysdate+1/24/60, sysdate+1/24/60/60 from dual;  
  
SYSDATE             SYSDATE+1/24        SYSDATE+1/24/60     SYSDATE+1/24/60/60  
------------------- ------------------- ------------------- -------------------  
2013-09-18 09:40:43 2013-09-18 10:40:43 2013-09-18 09:41:43 2013-09-18 09:40:44  
  
SQL> -- 获取本周第一天  
SQL> select trunc(sysdate, 'day') from dual;  
  
TRUNC(SYSDATE,'DAY'                                                               
-------------------                                                               
2013-10-13 00:00:00                                                               
  
SQL> -- 获取本周最后一天  
SQL> select trunc(sysdate, 'day') + 7 - 1/24/60/60 from dual;  
  
TRUNC(SYSDATE,'DAY'                                                               
-------------------                                                               
2013-10-19 23:59:59                                                               
  
SQL> -- 获取下周第 n 天  
SQL> select trunc(next_day(sysdate, 1)), trunc(next_day(sysdate, 7)) from dual;  
  
TRUNC(NEXT_DAY(SYSD TRUNC(NEXT_DAY(SYSD                                           
------------------- -------------------                                           
2013-10-20 00:00:00 2013-10-26 00:00:00                                           
  
SQL> --  
SQL> -- 获取上月第一天  
SQL> select trunc(add_months(sysdate, -1), 'month') from dual;  
  
TRUNC(ADD_MONTHS(SY                                                               
-------------------                                                               
2013-09-01 00:00:00                                                               
  
SQL> select to_date(to_char(add_months(sysdate, -1), 'yyyy-MM'), 'yyyy-MM') from dual;  
  
TO_DATE(TO_CHAR(ADD                                                               
-------------------                                                               
2013-09-01 00:00:00                                                               
  
SQL> -- 获取本月第一天  
SQL> select trunc(sysdate, 'month') from dual;  
  
TRUNC(SYSDATE,'MONT                                                               
-------------------                                                               
2013-10-01 00:00:00                                                               
  
SQL> select to_date(to_char(sysdate, 'yyyy-MM'), 'yyyy-MM') from dual;  
  
TO_DATE(TO_CHAR(SYS                                                               
-------------------                                                               
2013-10-01 00:00:00                                                               
  
SQL> -- 获取本月最后一天  
SQL> select trunc(last_day(sysdate)) + 1 - 1/24/60/60 from dual;  
  
TRUNC(LAST_DAY(SYSD                                                               
-------------------                                                               
2013-10-31 23:59:59                                                               
  
SQL> select add_months(trunc(sysdate, 'month'), 1) - 1/24/60/60 from dual;  
  
ADD_MONTHS(TRUNC(SY                                                               
-------------------                                                               
2013-10-31 23:59:59                                                               
  
SQL> --  
SQL> -- 获取本季度第一天  
SQL> select trunc(sysdate, 'Q') from dual;  
  
TRUNC(SYSDATE,'Q')                                                                
-------------------                                                               
2013-10-01 00:00:00                                                               
  
SQL> -- 获取本季度最后一天  
SQL> select add_months(trunc(sysdate, 'Q'), 3) - 1/24/60/60 from dual;  
  
ADD_MONTHS(TRUNC(SY                                                               
-------------------                                                               
2013-12-31 23:59:59                                                               
  
SQL> --  
SQL> -- 获取去年第一天(因为闰年平年天数不一样,所以不能使用加减 366或365天 来计算年份)  
SQL> select trunc(trunc(sysdate, 'year') - 1, 'year') from dual;  
  
TRUNC(TRUNC(SYSDATE                                                               
-------------------                                                               
2012-01-01 00:00:00                                                               
  
SQL> -- 获取今年第一天  
SQL> select trunc(sysdate, 'year') from dual;  
  
TRUNC(SYSDATE,'YEAR                                                               
-------------------                                                               
2013-01-01 00:00:00                                                               
  
SQL> select to_date(to_char(sysdate, 'yyyy')||'-01-01', 'yyyy-MM-dd') from dual;  
  
TO_DATE(TO_CHAR(SYS                                                               
-------------------                                                               
2013-01-01 00:00:00                                                               
  
SQL> -- 获取今年最后一天  
SQL> select trunc(add_months(sysdate, 12), 'year') - 1/24/60/60 from dual;  
  
TRUNC(ADD_MONTHS(SY                                                               
-------------------                                                               
2013-12-31 23:59:59                                                               
  
SQL> select to_date(to_char(sysdate, 'yyyy')||'-12-31 23:59:59', 'yyyy-MM-dd hh24:mi:ss') from dual;  
  
TO_DATE(TO_CHAR(SYS                                                               
-------------------                                                               
2013-12-31 23:59:59                                                               
  
SQL>   
SQL> -- 计算相差的天数  
SQL> select round(to_number(to_date('2013-10-19 23:59:59', 'yyyy-MM-dd hh24:mi:ss') - to_date('2013-10-18 00:00:00', 'yyyy-MM-dd hh24:mi:ss'))) value from dual;  
  
     VALUE                                                                        
----------                                                                        
         2                                                                        
  
SQL> -- 计算相差的小时数  
SQL> select round(to_number(to_date('2013-10-19 12:00:00', 'yyyy-MM-dd hh24:mi:ss') - to_date('2013-10-19 11:00:00', 'yyyy-MM-dd hh24:mi:ss')) * 24) value from dual;  
  
     VALUE                                                                        
----------                                                                        
         1                                                                        
  
SQL> -- 计算相差的分钟数  
SQL> select round(to_number(to_date('2013-10-19 12:00:00', 'yyyy-MM-dd hh24:mi:ss') - to_date('2013-10-19 11:30:00', 'yyyy-MM-dd hh24:mi:ss')) * 24 * 60) value from dual;  
  
     VALUE                                                                        
----------                                                                        
        30                                                                        
  
SQL>   

&nb

Oracle
MySQL
Access
SQLServer
DB2
Excel
SQLite
SYBASE
Postgres
如果你遇到数据库难题:
请访问www.zzzyk.com 试试
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,