Oracle时间运算
目录=========================================
1.Oracle的日期函数
2.日期加减
3.月份加减
4.年份加减
5.求每月的最后一天
6.求每月的第一天
7.求下一个星期几
入门知识：
①Oracle中的日期时间存储：
oracle数据库中存放时间格式的数据，是以oracle特定的格式存贮的，占7个字节，与查询时显示的时间格式无关。不存贮秒以下的时间单位。
②Oracle中的日期时间显示：
通常，客户端与数据库建立起连接后，oracle就会给一个缺省的时间格式数据的显示形式，与所使用的字符集有关。一般显示年月日，而不显示时分秒。
③Oracle中的日期时间插入：
向表中插入数据时，如果不使用转换函数，则时间字段的格式必须遵从会话环境的时间格式，否则不能插入。
④Oracle中的日期时间格式修改：
a.SQL>> b.册表\hkey_local_machine\software\oracle\home0主键中增加一个字串(8i版本)，字串名为nls_date_format，字串的值为你希望定义的时间格式
前者只对当前会话有效，也即是一旦你关闭了SQL*PLUS窗口或重新打开一个SQL*PLUS窗口，日期时间格式依然采用本地字符集对应的日期时间格式。后者对所有客户端应用有效。当两者同时应用时，以alter session的修改为准。
一、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
二、日期加减：
在Oralce中，对日期进行加减操作的默认单位是天，也就是说如果我们向当前日期加1的话是加上一天，而不是一秒或一小时。那么对一天中的一段时间进行加减要怎么做呢？很简单！只需将它们转化为以天为单位即可。
【1】为当前时间加上30分钟：
http://www.blogjava.net/Images/OutliningIndicators/None.gifSQL>select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss') now_date,http://www.blogjava.net/Images/OutliningIndicators/None.gif2to_char(sysdate+(30/24/60), 'yyyy-mm-dd hh:mi:ss') new_date
http://www.blogjava.net/Images/OutliningIndicators/None.gif3from dual;
http://www.blogjava.net/Images/OutliningIndicators/None.gif
http://www.blogjava.net/Images/OutliningIndicators/None.gifNOW_DATE NEW_DATE
http://www.blogjava.net/Images/OutliningIndicators/None.gif-------------------------------------- --------------------------------------
http://www.blogjava.net/Images/OutliningIndicators/None.gif2008-06-3010:47:312008-06-3011:17:31
http://www.blogjava.net/Images/OutliningIndicators/None.gif
http://www.blogjava.net/Images/OutliningIndicators/None.gifSQL>
我们看到了在绿色高亮处使用30/24/60将分钟转换成天。另外一个要注意的地方是：SQL*PLUS环境下默认的日期格式：NLS_DATE_FORMAT是DD-MM-YYYY，也即是不包含时、分、秒，所以我们这里必须采用to_char的方式指定输入的日期格式。
除此之外也可以通过在SQL*PLUS中执行下列语句修改默认的日期输出格式，这样的话就不需要通过to_char来转换了，直接输出就行。
http://www.blogjava.net/Images/OutliningIndicators/None.gifalter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; 【2】为当前时间减去30分钟：
http://www.blogjava.net/Images/OutliningIndicators/None.gifSQL>select to_char(sysdate+(-30/24/60),'yyyy-mm-dd hh:mi:ss') new_date from dual;http://www.blogjava.net/Images/OutliningIndicators/None.gif
http://www.blogjava.net/Images/OutliningIndicators/None.gifNEW_DATE
http://www.blogjava.net/Images/OutliningIndicators/None.gif--------------------------------------
http://www.blogjava.net/Images/OutliningIndicators/None.gif2008-06-3010:24:59
只需要加上一个负数即可以了。
三、月份加减：
月份的加减和日期加减相比要难了很多，因为每个月份的天数并不是固定的，可能是31,30,29,28。如果采用上面的方法将月份转换成实际天数将不可避免地出现多个判断，幸亏Oracle为我们提供了一个add_months函数，这个函数会自动判断月份的天数。看看下面的例子：
【1】为当前时间加上6个月：
http://www.blogjava.net/Images/OutliningIndicators/None.gifSQL>select add_months(sysdate, 6) from dual;http://www.blogjava.net/Images/OutliningIndicators/None.gif
http://www.blogjava.net/Images/OutliningIndicators/None.gifADD_MONTHS
http://www.blogjava.net/Images/OutliningIndicators/None.gif----------
http://www.blogjava.net/Images/OutliningIndicators/None.gif
31-12月-08 【2】为当前时间减去6个月：
http://www.blogjava.net/Images/OutliningIndicators/None.gifSQL>select add_months(sysdate, -6) from dual;http://www.blogjava.net/Images/OutliningIndicators/None.gif
http://www.blogjava.net/Images/OutliningIndicators/None.gifADD_MONTHS
http://www.blogjava.net/Images/OutliningIndicators/None.gif----------
http://www.blogjava.net/Images/OutliningIndicators/None.gif
31-12月-07 【3】求两个日期相差的月数：
通常情况下两个时间相减将得到以天数为单位的结果，可是有时我们更希望得到以月为单位的结果，如果手动转换这太麻烦了，所以Oracle又提供了一个函数，这个函数就是months_between。
http://www.blogjava.net/Images/OutliningIndicators/None.gifSQL>select months_between(sysdate,http://www.blogjava.net/Images/OutliningIndicators/None.gif2 to_date('2008-01-01 01:00:00', 'yyyy-mm-dd hh:mi:ss')) result
http://www.blogjava.net/Images/OutliningIndicators/None.gif3from dual;
http://www.blogjava.net/Images/OutliningIndicators/None.gif
http://www.blogjava.net/Images/OutliningIndicators/None.gif RESULT
http://www.blogjava.net/Images/OutliningIndicators/None.gif----------
http://www.blogjava.net/Images/OutliningIndicators/None.gif5.94928203
months_between函数有2个参数，第一个参数是结束日期，第二个参数是开始日期，Oracle用第一个参数减去第二个参数得到月份数。所以结果有可能会是负数的。
四、年份加减：
Oracle并不直接提供对年份进行加减的函数，不过有了add_months和months_between函数，我们照样可以做到。
【1】为当前日期加上2年：
http://www.blogjava.net/Images/OutliningIndicators/None.gifSQL>selectadd_months(sysdate, 2*12) two_years_laterhttp://www.blogjava.net/Images/OutliningIndicators/None.gif2from dual;
http://www.blogjava.net/Images/OutliningIndicators/None.gif
http://www.blogjava.net/Images/OutliningIndicators/None.gifTWO_YEARS_
http://www.blogjava.net/Images/OutliningIndicators/None.gif----------
http://www.blogjava.net/Images/OutliningIndicators/None.gif30-6月 -10
【2】求两个日期相差几年：
http://www.blogjava.net/Images/OutliningIndicators/None.gifSQL>select months_between(sysdate,http://www.blogjava.net/Images/OutliningIndicators/None.gif2 to_date('2006-06-30', 'yyyy-mm-dd')) /12 years_between
http://www.blogjava.net/Images/OutliningIndicators/None.gif3from dual;
http://www.blogjava.net/Images/OutliningIndicators/None.gif
http://www.blogjava.net/Images/OutliningIndicators/None.gifYEARS_BETWEEN
http://www.blogjava.net/Images/OutliningIndicators/None.gif-------------
http://www.blogjava.net/Images/OutliningIndicators/None.gif2
直接将两个日期相减，然后除以365天并不准确，但是不管一年有多少天它总是只有12个月，所以利用这一点我们可以先求出两个日期相差的月数，再除以12就得出相差的年数了
五、求每月的最后一天：
http://www.blogjava.net/Images/OutliningIndicators/None.gifSQL>select last_day(add_months(sysdate,2)) last_dayhttp://www.blogjava.net/Images/OutliningIndicators/None.gif2from dual;
http://www.blogjava.net/Images/OutliningIndicators/None.gif
http://www.blogjava.net/Images/OutliningIndicators/None.gifLAST_DAY
http://www.blogjava.net/Images/OutliningIndicators/None.gif----------
http://www.blogjava.net/Images/OutliningIndicators/None.gif31-8月 -08
六、求每月的第一天：
Oracle提供了last_day让我们能够求出所在月份的最后一天，但没有对应的first_day函数，如果有这方面的需求，只需要稍微动一下脑筋，利用last_day函数即可。例如下面的SQL语句就是求出下个月的第一天：
http://www.blogjava.net/Images/OutliningIndicators/None.gifSQL>selectlast_day(sysdate)+1fisrt_dayhttp://www.blogjava.net/Images/OutliningIndicators/None.gif2from dual;
http://www.blogjava.net/Images/OutliningIndicators/None.gif
http://www.blogjava.net/Images/OutliningIndicators/None.gifFISRT_DAY
http://www.blogjava.net/Images/OutliningIndicators/None.gif----------
http://www.blogjava.net/Images/OutliningIndicators/None.gif01-7月 -08
在这里我们将“每月的第一天”转换成“上个月最后一天的下一天”，问题就解决了！
七、求下一个星期几：
有时候我们会碰上“下个星期五是几号啊？”这样常见的问题。Oracle为此提供了一个函数：next_day，它的语法是这样的：next_day(date, string)。其中第一个参数date告诉Oracle从什么时候开始算起，第二个参数string则告诉Oracle要取的工作日。
下面我们看看如何得到下个星期五的日期：
http://www.blogjava.net/Images/OutliningIndicators/None.gifSQL>select next_day(sysdate, 'Friday') "Next Friday" from dual;http://www.blogjava.net/Images/OutliningIndicators/None.gifselect next_day(sysdate, 'Friday') "Next Friday" from dual
http://www.blogjava.net/Images/OutliningIndicators/None.gif*
http://www.blogjava.net/Images/OutliningIndicators/None.gifERROR at line 1:
http://www.blogjava.net/Images/OutliningIndicators/None.gifORA-01846: 周中的日无效
很奇怪！是不？明明语法没有问题，但为什么会说“周中的日无效”呢？这里就不得不说到Oracle中的语言和时区的问题了。下面这张图是使用TOAD截取出来的客户端session的语言和时区信息：
http://www.blogjava.net/images/blogjava_net/pengpenglin/%E6%95%B0%E6%8D%AE%E5%BA%93/oracle_timezone.jpg
图一
从图中我们知道了客户端的语言是简体中文，日期使用的语言也是简体中文，这就是为什么上面的SQL语句出错的原因了，因为在中文中只有“星期一，星期二”这样的工作日表示，而没有“Monday，Firday”这样的写法！
http://www.blogjava.net/Images/OutliningIndicators/None.gifSQL>select next_day(sysdate,'星期五') "下周五" from dual;http://www.blogjava.net/Images/OutliningIndicators/None.gif
http://www.blogjava.net/Images/OutliningIndicators/None.gif下周五
http://www.blogjava.net/Images/OutliningIndicators/None.gif----------
http://www.blogjava.net/Images/OutliningIndicators/None.gif04-7月 -08
如果你不确定自己的时区或者你担心从一个时区移植到另一个时区时，SQL语句会出错，Oracle还允许你用数字的形式来表示工作日。但是要记得一点：1表示的是周日，2表示的是周一，3表示的是周二，依此类推。
例如我要查下个周三是什么时候，则函数是这样写的：next_day(sysdate, 4)。
http://www.blogjava.net/Images/OutliningIndicators/None.gifSQL>selectnext_day(sysdate,4)from dual;http://www.blogjava.net/Images/OutliningIndicators/None.gif
http://www.blogjava.net/Images/OutliningIndicators/None.gifNEXT_DAY(S
http://www.blogjava.net/Images/OutliningIndicators/None.gif----------
http://www.blogjava.net/Images/OutliningIndicators/None.gif02-7月 -08
http://www.blogjava.net/images/blogjava_net/pengpenglin/%E6%95%B0%E6%8D%AE%E5%BA%93/oracle_nextday_function.jpg
二、Oracle中的Round和Trunc：
如同对数字进行四舍五入和按位截取一样，Oracle对时间日期也提供了这两种功能。但比起对数字进行四舍五入和截取比较复杂：这是因为时间日期是有格式的。下面看看这两个函数的定义和用途：
ROUND(date [, format])
TRUNC(date [, format])
Round函数对日期进行“四舍五入”，Trunc函数对日期进行截取。如果我们不指定格式的话，Round会返回一个最接近date参数的日期，而Trunc函数只会简单的截取时分秒部分，返回年月日部分。
二、Round和Trunc函数示例：
http://www.blogjava.net/Images/OutliningIndicators/None.gifSQL>select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') now_date,http://www.blogjava.net/Images/OutliningIndicators/None.gif2 to_char(Round(sysdate),'yyyy-mm-dd hh24:mi:ss') round_date,
http://www.blogjava.net/Images/OutliningIndicators/None.gif3 to_char(Trunc(sysdate),'yyyy-mm-dd hh24:mi:ss') trunc_date
http://www.blogjava.net/Images/OutliningIndicators/None.gif4from dual;
http://www.blogjava.net/Images/OutliningIndicators/None.gif
http://www.blogjava.net/Images/OutliningIndicators/None.gifNOW_DATE ROUND_DATE TRUNC_DATE
http://www.blogjava.net/Images/OutliningIndicators/None.gif-------------------------------------- -------------------------------------- ----------------------
http://www.blogjava.net/Images/OutliningIndicators/None.gif2008-06-3014:52:132008-07-0100:00:002008-06-3000:00:00
http://www.blogjava.net/Images/OutliningIndicators/None.gif
这是一个典型的例子，由于我们没有指定round和trunc函数的格式，所以Oracle默认采用了按日期时间的格式，该例子中当前的时间是下午14:52分，已经超过了12:00 AM这个中界线，所以Round返回07-01日而非06-30日。而Trunc不管三七二十一直接截取前面日期部分返回。
另外一个值得注意的地方是这两个函数返回的时分秒都是00:00:00，即一天的开始时间(对于12小时制的返回的是12:00:00 AM)。
三、指定格式的Round和Trunc函数示例：
如果我们对Round函数和Trunc函数指定了格式，事情就变得有点复杂了，不过核心思想还是不变：Round是四舍五入，Trunc是截取。举个例子来说，假如我们以年为格式，则现在Oracle的判断是基于年来判断，超过一年的一半(即6月30日)，Round函数则返回下一年了，Trunc函数依然返回当前年。
http://www.blogjava.net/Images/OutliningIndicators/None.gifSQL>select sysdate "Now date",http://www.blogjava.net/Images/OutliningIndicators/None.gif2Round(sysdate, 'yyyy') Round_year,
http://www.blogjava.net/Images/OutliningIndicators/None.gif3 Trunc(sysdate, 'yyyy') Trunc_year
http://www.blogjava.net/Images/OutliningIndicators/None.gif4from dual;
http://www.blogjava.net/Images/OutliningIndicators/None.gif
http://www.blogjava.net/Images/OutliningIndicators/None.gifNow date ROUND_YEAR TRUNC_YEAR
http://www.blogjava.net/Images/OutliningIndicators/None.gif---------- ---------- ----------
http://www.blogjava.net/Images/OutliningIndicators/None.gif30-6月 -0801-1月 -0801-1月 -08
关于这两个函数可用的格式非常多，但日常应用中用得比较多的基本上就这几个，以Round函数为例：
http://www.blogjava.net/Images/OutliningIndicators/None.gifselectRound(sysdate, 'Q') Rnd_Q,http://www.blogjava.net/Images/OutliningIndicators/None.gifRound(sysdate, 'Month') Rnd_Month,
http://www.blogjava.net/Images/OutliningIndicators/None.gifRound(sysdate, 'WW') Rnd_Week,
http://www.blogjava.net/Images/OutliningIndicators/None.gifRound(sysdate, 'W') Rnd_Week_again,
http://www.blogjava.net/Images/OutliningIndicators/None.gifRound(sysdate, 'DDD') Rnd_day,
http://www.blogjava.net/Images/OutliningIndicators/None.gifRound(sysdate, 'DD') Rnd_day_again,
http://www.blogjava.net/Images/OutliningIndicators/None.gifRound(sysdate, 'DAY') Rnd_day_of_week,
http://www.blogjava.net/Images/OutliningIndicators/None.gifRound(sysdate, 'D') Rnd_day_of_week_again,
http://www.blogjava.net/Images/OutliningIndicators/None.gifRound(sysdate, 'HH12') Rnd_hour_12,
http://www.blogjava.net/Images/OutliningIndicators/None.gifRound(sysdate, 'HH24') Rnd_hour_24,
http://www.blogjava.net/Images/OutliningIndicators/None.gifRound(sysdate, 'MI') Rnd_minute
http://www.blogjava.net/Images/OutliningIndicators/None.giffrom dual
页:
[1]