Oracle时间运算

快来打我* 2022-06-16 11:52 989阅读 0赞

【原】Oracle开发专题之：时间运算  
目录  
=========================================  
1.Oracle的日期函数  
2.日期加减  
3.月份加减  
4.年份加减  
5.求每月的最后一天  
6.求每月的第一天  
7.求下一个星期几

入门知识：

①Oracle中的日期时间存储：  
oracle数据库中存放时间格式的数据，是以oracle特定的格式存贮的，占7个字节，与查询时显示的时间格式无关。不存贮秒以下的时间单位。  
②Oracle中的日期时间显示：  
通常，客户端与数据库建立起连接后，oracle就会给一个缺省的时间格式数据的显示形式，与所使用的字符集有关。一般显示年月日，而不显示时分秒。  
③Oracle中的日期时间插入：  
向表中插入数据时，如果不使用转换函数，则时间字段的格式必须遵从会话环境的时间格式，否则不能插入。  
④Oracle中的日期时间格式修改：  
a.SQL> alter session set nls\_date\_format = 'yyyy-mm-dd hh24:mi:ss';  
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分钟： 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(add\_months(sysdate,2)) 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  
2 from dual;

FISRT\_DAY  
\----------  
01-7月 -08  
在这里我们将“每月的第一天”转换成“上个月最后一天的下一天”，问题就解决了！

七、求下一个星期几：

有时候我们会碰上“下个星期五是几号啊？”这样常见的问题。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

Oracle时间与日期的相关操作实例2010-03-31 18:47 佚名 互联网 我要评论(0) 字号：T | T  
在Oracle时间与日期的相关操作中你对其实际的操作方案是否了解，以及是否在Oracle时间与日期的应用中存有疑惑？如果你想了解相关的内容，以下的相关内容就是对其具体的介绍。

AD：

以下相关内容就是对Oracle时间与日期的相关操作的详细介绍，如果你对Oracle时间与日期的相关操作有不解之处的地方，你可以浏览以下的文章对其的实际应用有一个更好的了解，以下就是文章的内容介绍。

sysdate+(5/24/60/60) 在系统时间基础上延迟5秒

sysdate+5/24/60 在系统时间基础上延迟5分钟

sysdate+5/24 在系统时间基础上延迟5小时

sysdate+5 在系统时间基础上延迟5天

add\_months(sysdate,-5) 在系统时间基础上延迟5月

add\_months(sysdate,-5\*12) 在系统时间基础上延迟5年

上月末的日期：

select last\_day(add\_months(sysdate, -1)) from dual;  
本月的最后一秒：

select trunc(add\_months(sysdate,1),'MM') - 1/24/60/60 from dual  
本周星期一的日期：

select trunc(sysdate,'day')+1 from dual

年初至今的天数：

select ceil(sysdate - trunc(sysdate, 'year')) from dual;  
今天是今年的第几周 :

select to\_char(sysdate,'fmww') from dual 今天是本月的第几周:

SELECT TO\_CHAR(SYSDATE,'WW') - TO\_CHAR(TRUNC(SYSDATE,'MM'),'WW') + 1 AS "weekOfMon" FROM dual

本月的天数

SELECT to\_char(last\_day(SYSDATE),'dd') days FROM dual  
今年的天数

select add\_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual  
Oracle时间在下个星期一的日期

SELECT Next\_day(SYSDATE,'monday') FROM dual

计算工作日方法

create table t(s date,e date); alter session set nls\_date\_format = 'yyyy-mm-dd'; insert into t values('2003-03-01','2003-03-03'); insert into t values('2003-03-02','2003-03-03'); insert into t values('2003-03-07','2003-03-08'); insert into t values('2003-03-07','2003-03-09'); insert into t values('2003-03-05','2003-03-07'); insert into t values('2003-02-01','2003-03-31');

以上的相关内容就是对Oracle时间的介绍，望你能有所收获。