计算机教程

当前位置:3522.com > 计算机教程 > Oracle时间运算

Oracle时间运算

来源:http://www.4sports-uk.com 作者:3522.com 时间:2020-05-01 14:17

由于DB2数据库使用的人太少,公司有没有专业的DBA,决定把数据库从DB2数据库切换为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_machinesoftwareoraclehome0主键中增加一个字串(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分钟:

3522.com 1SQL> select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss') now_date,
3522.com 2  2             to_char(sysdate (30/24/60), 'yyyy-mm-dd hh:mi:ss') new_date
3522.com 3  3    from dual;
3522.com 4
3522.com 5NOW_DATE                               NEW_DATE
3522.com 6-------------------------------------- --------------------------------------
3522.com 72008-06-30 10:47:31                    2008-06-30 11:17:3522.com,31
3522.com 8
3522.com 9SQL> 

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

除此之外也可以通过在SQL*PLUS中执行下列语句修改默认的日期输出格式,这样的话就不需要通过to_char来转换了,直接输出就行。

3522.com 10alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

【2】为当前时间减去30分钟:

3522.com 11SQL> select to_char(sysdate (-30/24/60),'yyyy-mm-dd hh:mi:ss') new_date from dual;
3522.com 12
3522.com 13NEW_DATE
3522.com 14--------------------------------------
3522.com 152008-06-30 10:24:59

只需要加上一个负数即可以了。

三、月份加减:

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

【1】为当前时间加上6个月:

3522.com 16SQL> select add_months(sysdate, 6) from dual;
3522.com 17
3522.com 18ADD_MONTHS
3522.com 19----------
3522.com 2031-12月-08

【2】为当前时间减去6个月:

3522.com 21SQL> select add_months(sysdate, -6) from dual;
3522.com 22
3522.com 23ADD_MONTHS
3522.com 24----------
3522.com 2531-12月-07

【3】求两个日期相差的月数:

通常情况下两个时间相减将得到以天数为单位的结果,可是有时我们更希望得到以月为单位的结果,如果手动转换这太麻烦了,所以Oracle又提供了一个函数,这个函数就是months_between。

3522.com 26SQL> select months_between(sysdate,
3522.com 27  2         to_date('2008-01-01 01:00:00', 'yyyy-mm-dd hh:mi:ss')) result
3522.com 28  3    from dual;
3522.com 29
3522.com 30    RESULT
3522.com 31----------
3522.com 325.94928203

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

四、年份加减:

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

【1】为当前日期加上2年:

3522.com 33SQL> select add_months(sysdate, 2*12) two_years_later
3522.com 34  2      from dual;
3522.com 35
3522.com 36TWO_YEARS_
3522.com 37----------
3522.com 3830-6月 -10

【2】求两个日期相差几年:

3522.com 39SQL> select months_between(sysdate, 
3522.com 40  2         to_date('2006-06-30', 'yyyy-mm-dd')) / 12 years_between
3522.com 41  3    from dual;
3522.com 42
3522.com 43YEARS_BETWEEN
3522.com 44-------------
3522.com 45            2

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

五、求每月的最后一天:

3522.com 46SQL> select last_day(add_months(sysdate,2)) last_day
3522.com 47  2    from dual;
3522.com 48
3522.com 49LAST_DAY
3522.com 50----------
3522.com 5131-8月 -08

六、求每月的第一天:

Oracle提供了last_day让我们能够求出所在月份的最后一天,但没有对应的first_day函数,如果有这方面的需求,只需要稍微动一下脑筋,利用last_day函数即可。例如下面的SQL语句就是求出下个月的第一天:

3522.com 52SQL> select last_day(sysdate) 1 fisrt_day
3522.com 53  2      from dual;
3522.com 54
3522.com 55FISRT_DAY
3522.com 56----------
3522.com 5701-7月 -08

在这里我们将“每月的第一天”转换成“上个月最后一天的下一天”,问题就解决了!

七、求下一个星期几:

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

下面我们看看如何得到下个星期五的日期:

3522.com 58SQL> select next_day(sysdate, 'Friday') "Next Friday" from dual;
3522.com 59select next_day(sysdate, 'Friday') "Next Friday" from dual
3522.com 60                         *
3522.com 61ERROR at line 1:
3522.com 62ORA-01846: 周中的日无效

很奇怪!是不?明明语法没有问题,但为什么会说“周中的日无效”呢?这里就不得不说到Oracle中的语言和时区的问题了。下面这张图是使用TOAD截取出来的客户端session的语言和时区信息:

3522.com 63
图一

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

3522.com 64SQL> select next_day(sysdate,'星期五') "下周五" from dual;
3522.com 65
3522.com 66下周五
3522.com 67----------
3522.com 6804-7月 -08

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

例如我要查下个周三是什么时候,则函数是这样写的:next_day(sysdate, 4)。

3522.com 69SQL> select next_day(sysdate,4) from dual;
3522.com 70
3522.com 71NEXT_DAY(S
3522.com 72----------
3522.com 7302-7月 -08

3522.com 74

看一下日历是不是正确的,确实不错!呵呵

由于系统底层使用的是ORM映射工具,由于没有使用存储过程,自定义函数,触发器,因此我以为系统改动不大,但发现的问题却不少。

1、我们的主键基本上都采用共的是Sequence,没有采用自动增长作为主键。

但获取Sequence在两种数据库中是不相同的。

DB2获取的方法

values next value for eas.seq_SequenceNameOracle中获取的方法

select seq_SequenceName.nextval from dual2我们底层采用的是ORM映射工具ActiveRecord,发现执行SQL时语句末尾不能有分号,太奇怪了。这个问题似乎是ActiveRecord本身的问题

例如:

 /// summary /// 得到第一行第一列的值 /// /summary /// param name="sqlstr"/param /// returns/returns public object ScalarBySQL(String sqlstr) { ISession sess = ActiveRecordMediator.GetSessionFactoryHolder().CreateSession(typeof(ActiveRecordBase)); IDbConnection dbconn = sess.Connection; try { IDbCommand dbCommand = dbconn.CreateCommand(); dbCommand.CommandText = sqlstr; object thisReader = dbCommand.ExecuteScalar(); return thisReader; } catch (Exception ex) { LogInfo.Error(sqlstr   ex.Message); throw new Exception("数据库执行语句错误"); } finally { dbconn.Close(); } }

例如:sqlstr="select * FROM EAS.T_HRWagesLog where HRWL_Month='201603'";

时在DB2,Oracle中都能正常执行,但如果末尾有一个分号;则在Oracle中直接就是一个错误

ORA-00911: 无效字符

3、部分数据库类型不支持,部分函数没有。

没有XML,数据类型。双精度浮点数字数据类型在DB2是DOUBLE,在Oracle是BINARY_DOUBLE

由于使用了一个工具导出的DOUBLE,直接转为了NUMBER(13),导致没有小数部分,导致了大量错误。

本文由3522.com发布于计算机教程,转载请注明出处:Oracle时间运算

关键词: 3522.com