ORACLE时间计算相关函数整理

1. trunc()函数；
2. round()函数；
3. EXTRACT（）函数；
4. to_char()函数；
5. interval 间隔计算；

 

-- 获取时间

 

 

select sysdate "sysdate" from dual;
sysdate
-------------------------
2013/8/5 10:20:14

 

 

 

-- 日期精确到 年 月 日 时 分 秒 季度 世纪

-- trunc() & round() 这两个函数经常被使用在数值处理中，日期类型中同样适用。

TRUNC(date [, fmt ])

ROUND(date [, fmt ])

fmt列表如下:

 

Table 5-14 lists the format models you can use with the ROUND and TRUNC date functions and the units to which they round and truncate dates. The default model, 'DD', returns the date rounded or truncated to the day with a time of midnight.

Table 5-14 Date Format Models for the ROUND and TRUNC Date Functions

Format Model Rounding or Truncating Unit

CC SCC	One greater than the first two digits of a four-digit year
SYYYY YYYY YEAR SYEAR YYY YY Y	Year (rounds up on July 1)
IYYY IY IY I	ISO Year
Q	Quarter (rounds up on the sixteenth day of the second month of the quarter)
MONTH MON MM RM	Month (rounds up on the sixteenth day)
WW	Same day of the week as the first day of the year
IW	Same day of the week as the first day of the ISO year
W	Same day of the week as the first day of the month
DDD DD J	Day
DAY DY D	Starting day of the week
HH HH12 HH24	Hour
MI	Minute

 

--************************************************************************************************************

-- 提取当前日期中的 年 月 日 时 分 秒 当前年中第几周 当前日期是周几 等 可使用 to_char()  和 EXTRACT（）

语法：

EXTRACT( { { YEAR
           | MONTH
           | DAY
           | HOUR
           | MINUTE
           | SECOND
           }
         | { TIMEZONE_HOUR
           | TIMEZONE_MINUTE
           }
         | { TIMEZONE_REGION
           | TIMEZONE_ABBR
           }
         }
         FROM { datetime_value_expression
              | interval_value_expression
              }
       )

例句：

The following example returns from the oe.orders table the number of orders placed in each month:

SELECT EXTRACT(month FROM order_date) "Month",
  COUNT(order_date) "No. of Orders"
  FROM orders
  GROUP BY EXTRACT(month FROM order_date)
  ORDER BY "No. of Orders" DESC;

     Month No. of Orders
---------- -------------
        11            15
         7            14
         6            14
         3            11
         5            10
         9             9
         2             9
         8             7
        10             6
         1             5
        12             4
         4             1
 
12 rows selected.

The following example returns the year 1998.

SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL;

EXTRACT(YEARFROMDATE'1998-03-07')
---------------------------------
                             1998

The following example selects from the sample table hr.employees all employees who were hired after 1998:

SELECT last_name, employee_id, hire_date
   FROM employees
   WHERE EXTRACT(YEAR FROM
   TO_DATE(hire_date, 'DD-MON-RR')) > 1998
   ORDER BY hire_date;


LAST_NAME                 EMPLOYEE_ID HIRE_DATE
------------------------- ----------- ---------
Landry                            127 14-JAN-99
Lorentz                           107 07-FEB-99
Cabrio                            187 07-FEB-99
. . .

The following example results in ambiguity, so Oracle returns UNKNOWN:

SELECT EXTRACT(TIMEZONE_REGION 
      FROM TIMESTAMP '1999-01-01 10:00:00 -08:00')
   FROM DUAL;

EXTRACT(TIMEZONE_REGIONFROMTIMESTAMP'1999-01-0110:00:00-08:00')
----------------------------------------------------------------
UNKNOWN

 

-- to_char() 函数处理日期 

语法：

TO_CHAR({ datetime | interval } [, fmt [, 'nlsparam' ] ])

interval这种参数不常使用这里就不做讨论了，如有需要可以参见官方文档的例子。

 

SQL> select to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss:pm:dy') from dual; 
TO_CHAR(SYSDATE,'YYYY-MM-DD:HH
---------------------------------------
2013-08-05:11:05:38:上午:星期一

 

 

下面是一些常用的例子：

fmt = 'yyyy' -- 显示datetime的当前年 4位字符
fmt = 'yyy'  -- 显示datetime的当前年 后三位字符
fmt = 'yy'   -- 显示datetime的当前年 后两位字符
fmt = 'y'    -- 显示datetime的当前年 最后一位字符
fmt = 'Q'    -- 显示datetiem所在季度
fmt = 'mm'   -- 显示datetime的当前月份
fmt = 'IW'   -- 显示datetime的周 （按日历上的那种每年有52或53周 例如20130107为日历上2013年的第二周）
fmt = 'WW'   -- 显示datetime为本年中第几个星期/七天（本年1月1号至1月7号为本年第一个星期 非日历上的正常周）
fmt = 'W'    -- 显示datetime为本月中第几个星期/七天(1号到7号为第一个星期 或者说是第一个七天)
fmt = 'ddd'  -- 显示datetime的当前日期在本年中的第几天
fmt = 'dd'   -- 显示datetime的当前日期中的天
fmt = 'd'    -- 显示datetime是当前周的第几天 （周日为第一天）
fmt = 'hh'   -- 显示datetime小时 12小时制
fmt = 'hh24' -- 显示datetime小时 24小时制 
fmt = 'mi'   -- 显示datetime分钟
fmt = 'ss'   -- 显示datetime秒

 

IW与WW 区别 可见             http://www.360doc.com/content/13/0805/11/764260_304857194.shtml

 

例句

select to_char(sysdate,'yyyy') from dual;
TO_CHAR(SYSDATE,'YYYY')
-----------------------
2013

   

SQL> select to_char(sysdate,'yy') from dual;
TO_CHAR(SYSDATE,'YY')
---------------------
13

   

----***********************************************************************************

-- 时间间隔计算

  

   
-- 当前时间减去5年的时间
　　 select   sysdate,sysdate - interval '5' year  from dual;
　　当前时间减去5月的时间
　　 select   sysdate,sysdate - interval '5' month  from dual;
　　当前时间减去5天的时间
　　 select   sysdate - interval '5' day  from dual;
-- 当前时间减去5小时的时间
　　 select   sysdate - interval '5' hour  from dual;
-- 当前时间减去5分钟的时间
　　 select   sysdate,sysdate - interval '5' MINUTE  from  dual;
-- 当前时间减去5秒时间
    select   sysdate,sysdate - interval '5' second  from dual;
-- 时间间隔乘以一个数字
　　 select   sysdate,sysdate - 4*interval '5' MINUTE  from dual

 -- 计算某月最后一天

 

-- 计算某月最后一天
select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
-----------------
2013/8/31 11:51:5

 

 

待续...... 欢迎补充