oracle 时间处理

一、Oracle的日期函数:


Oracle从8i开始就提供了大量的日期函数，这些日期函数包括对日期进行加减、转换、截取等功能。下面是Oracle提供的日期函数一览表  Function
 Use
 
ADD_MONTHS
 Adds months to a date
 
LAST_DAY
 Computes the last day of the month
 
MONTHS_BETWEEN
 Determines the number of months between two dates
 
NEW_TIME
 Translates a time to a new time zone
 
NEXT_DAY
 Returns the date of the next specified weekday 
 
ROUND
 Rounds a date/time value to a specified element
 
SYSDATE
 Returns the current date and time
 
TO_CHAR
 Converts dates to strings
 
TO_DATE
 Converts strings and numbers to dates
 
TRUNC
 Truncates a date/time value to a specific element
 

TRUNC(TO_DATE('24-Nov-1999 08:00 pm','dd-mon-yyyy hh:mi am'))

='24-Nov-1999 12:00:00 am'

TRUNC(TO_DATE('24-Nov-1999 08:37 pm','dd-mon-yyyy hh:mi am'),'hh') ='24-Nov-1999 08:00:00 am'

trunc(sysdate,'yyyy') --返回当年第一天.

trunc(sysdate,'mm') --返回当月第一天.

trunc(sysdate,'d') --返回当前星期的第一天.

trunc(sysdate,'dd')--返回当前年月日

trunc(sysdate, 'hh')--返回当前小时

trunc(sysdate, 'mi')--返回当前分钟

二、日期加减：


在Oralce中，对日期进行加减操作的默认单位是天，也就是说如果我们向当前日期加1的话是加上一天，而不是一秒或一小时。那么对一天中的一段时间进行加减要怎么做呢？很简单！只需将它们转化为以天为单位即可。


【1】为当前时间加上30分钟： SQL> select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss') now_date,
  2             to_char(sysdate+(30/24/60), 'yyyy-mm-dd hh:mi:ss') new_date
  3    from dual;


NOW_DATE                               NEW_DATE
-------------------------------------- --------------------------------------
2008-06-30 10:47:31                    2008-06-30 11:17:31


SQL> 
我们看到了在绿色高亮处使用30/24/60将分钟转换成天。另外一个要注意的地方是：SQL*PLUS环境下默认的日期格式：NLS_DATE_FORMAT是DD-MM-YYYY，也即是不包含时、分、秒，所以我们这里必须采用to_char的方式指定输入的日期格式。


除此之外也可以通过在SQL*PLUS中执行下列语句修改默认的日期输出格式，这样的话就不需要通过to_char来转换了，直接输出就行。 alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
【2】为当前时间减去30分钟： SQL> select to_char(sysdate+(-30/24/60),'yyyy-mm-dd hh:mi:ss') new_date from dual;


NEW_DATE
--------------------------------------
2008-06-30 10:24:59
只需要加上一个负数即可以了。


三、月份加减：


月份的加减和日期加减相比要难了很多，因为每个月份的天数并不是固定的，可能是31,30,29,28。如果采用上面的方法将月份转换成实际天数将不可避免地出现多个判断，幸亏Oracle为我们提供了一个add_months函数，这个函数会自动判断月份的天数。看看下面的例子：


【1】为当前时间加上6个月： SQL> select add_months(sysdate, 6) from dual;


ADD_MONTHS
----------
31-12月-08
【2】为当前时间减去6个月： SQL> select add_months(sysdate, -6) from dual;


ADD_MONTHS
----------
31-12月-07
【3】求两个日期相差的月数：


通常情况下两个时间相减将得到以天数为单位的结果，可是有时我们更希望得到以月为单位的结果，如果手动转换这太麻烦了，所以Oracle又提供了一个函数，这个函数就是months_between。 SQL> select months_between(sysdate,
  2         to_date('2008-01-01 01:00:00', 'yyyy-mm-dd hh:mi:ss')) result
  3    from dual;


    RESULT
----------
5.94928203
months_between函数有2个参数，第一个参数是结束日期，第二个参数是开始日期，Oracle用第一个参数减去第二个参数得到月份数。所以结果有可能会是负数的。


四、年份加减：


Oracle并不直接提供对年份进行加减的函数，不过有了add_months和months_between函数，我们照样可以做到。


【1】为当前日期加上2年： SQL> select add_months(sysdate, 2*12) two_years_later
  2      from dual;


TWO_YEARS_
----------
30-6月 -10
【2】求两个日期相差几年： SQL> select months_between(sysdate, 
  2         to_date('2006-06-30', 'yyyy-mm-dd')) / 12 years_between
  3    from dual;


YEARS_BETWEEN
-------------
            2
直接将两个日期相减，然后除以365天并不准确，但是不管一年有多少天它总是只有12个月，所以利用这一点我们可以先求出两个日期相差的月数，再除以12就得出相差的年数了


五、求每月的最后一天：


SQL> select last_day(sysdate) last_day
  2    from dual;


LAST_DAY
----------
31-8月 -08
六、求每月的第一天：


Oracle提供了last_day让我们能够求出所在月份的最后一天，但没有对应的first_day函数，如果有这方面的需求，只需要稍微动一下脑筋，利用last_day函数即可。例如下面的SQL语句就是求出下个月的第一天： SQL> select last_day(sysdate)+1 fisrt_day
        from dual;
求当前月的第一天：
select last_day(add_months(sysdate,-1))+1 from dual
在这里我们将“每月的第一天”转换成“上个月最后一天的下一天”，问题就解决了！


七、求下一个星期几：


有时候我们会碰上“下个星期五是几号啊？”这样常见的问题。Oracle为此提供了一个函数：next_day，它的语法是这样的：next_day(date, string)。其中第一个参数date告诉Oracle从什么时候开始算起，第二个参数string则告诉Oracle要取的工作日。


下面我们看看如何得到下个星期五的日期：
SQL> select next_day(sysdate, 'Friday') "Next Friday" from dual;
select next_day(sysdate, 'Friday') "Next Friday" from dual
                         *
ERROR at line 1:
ORA-01846: 周中的日无效
很奇怪！是不？明明语法没有问题，但为什么会说“周中的日无效”呢？这里就不得不说到Oracle中的语言和时区的问题了。下面这张图是使用TOAD截取出来的客户端session的语言和时区信息：




图一


从图中我们知道了客户端的语言是简体中文，日期使用的语言也是简体中文，这就是为什么上面的SQL语句出错的原因了，因为在中文中只有“星期一，星期二”这样的工作日表示，而没有“Monday，Firday”这样的写法！


SQL> select next_day(sysdate,'星期五') "下周五" from dual;


下周五
----------
04-7月 -08
如果你不确定自己的时区或者你担心从一个时区移植到另一个时区时，SQL语句会出错，Oracle还允许你用数字的形式来表示工作日。但是要记得一点：1表示的是周日，2表示的是周一，3表示的是周二，依此类推。


例如我要查下个周三是什么时候，则函数是这样写的：next_day(sysdate, 4)。 SQL> select next_day(sysdate,4) from dual;


NEXT_DAY(S
----------
02-7月 -08