Oracle 提供了三个用于存储日期/时间值的数据类型:
表示日期和时间的 DATE 类型。 DATE 字段中存储的值包含有与世纪、年、月、日、小时、分钟和秒相对应的"组成部分"。
日期可以是公元前 4712 年 1 月 1 日到公元 9999 年 12 月 31 日这一范围中的任何一天。
从 Oracle9i 开始提供的 TIMESTAMP 类型是 DATE 类型的有效扩展格式,并且符合 ANSI SQL 标准。
它提供了更大的时间精度,支持多达九位的小数,同时还能够存储时区信息。
从 Oracle9i 开始提供的 INTERVAL
类型,它支持存储时间差(如"两年零五个月"或"三天零十八个小时零四十五分钟"),并可以与 DATE 或 TIMESTAMP
进行加法运算以生成一个新的 DATE / TIMESTAMP 值。
本文主要介绍 DATE 类型,但适用于 DATE 的大部分功能也适用于 TIMESTAMP。 (有关 TIMESTAMP 和
INTERVAL 类型的更多背景知识,请阅读 Jonathan Gennick 在 Oracle 杂志上发表的文章"Datetime
Datatypes Add Precision"和"Finding the Time in Between"(这两篇文章均在 2002 年
11 月 - 12 月刊中发表)。
Oracle 如何存储 DATE。 对于 Oracle 中的 DATE
类型,首先要了解的是,它的内部表示形式使它可以通过不同的方法进行显示和操作。 它有效地独立于任何特定的字符串格式。 如果使用 SELECT
选择一个 DATE 类型,Oracle 将自动把它转换为可读字符串,但这并不是值的实际存储方法。
使用 SYSDATE 选择当前的系统时间,SYSDATE 返回 DATE 类型的值,是数据库所在的操作系统的当前日期和时间:
SELECT SYSDATE FROM dual /* e.g. 25-JUL-05 */
该格式受 Oracle 参数 NLS_DATE_FORMAT 的控制,可以根据每个会话进行更改(如下所示)。 了解一下内部表示形式:
SELECT DUMP(SYSDATE) FROM dual
/* e.g. Typ=13 Len=8: 213,7,7,25,23,7,15,0 */
结果中由逗号分隔的值对应于 Oracle 存储日期和时间的每个部分(从世纪一直到秒)所使用的字节。
此处有一个重要问题值得注意:在比较 DATE 类型时,将比较 DATE 的所有组成部分(一直比较到秒)。
在某些情况下,您可能要根据不同的时间单位(如年、月或日)比较两个日期。
这种情况下,可以使用 TRUNC 这样的函数对所比较的两个 DATE 的小时、分钟和秒部分进行向下取整。
有关更多详细信息,请参阅下面的"日期运算"。
如果您熟悉面向对象的编程,则也可将 DATE 类型看作是对象。 他们全都拥有属性(年、月、小时等)和行为,如:
SELECT SYSDATE - 7 FROM dual /* e.g. 18-JUL-05 */
该示例返回七天前的日期。 其它的"行为"包括 DATE 比较,这意味着您可以对日期执行 SORT BY、GROUP BY
以及查找界于两个日期之间的日期 (BETWEEN) 等,还可以进行减法操作: 从一个 DATE 中减去另一个 DATE
以获得整数的日期差(在使用 TIMESTAMP 时,将获得 INTERVAL 类型)。
在 DATE 类型和字符串之间进行转换。TO_DATE() 和 TO_CHAR() 函数用于在 Oracle
DATE"对象"和使用者可理解的日期字符串之间进行转换。
这两个函数均使用三个参数,一个要转换的值、一个可选的格式掩码和一个用于指定语言的可选字符串(例如,FRENCH)。
从概念上而言,格式掩码类似于正则表达式;您指定日期模式,该模式指示 Oracle 如何将匹配的字符串与 DATE 类型关联。 Oracle
数据库 SQL 参考的"格式模型"中介绍了格式掩码。
使用 TO_CHAR。 以下是一个简单示例,它再次使用了 SYSDATE 函数:
SELECT TO_CHAR( SYSDATE, 'YYYY-MM-DD HH24:MI:SS' ) FROM dual
/* e.g. 2005-07-26 17:34:04 */
详细了解一下该格式掩码,"YYYY"表示一个四位年份,"MM"表示一个两位月份,"DD"表示一个两位的月份中的日,"HH24"表示 24
小时制的小时,"MI"表示 0 和 59 之间的分钟,"SS"表示 0 和 59 之间的秒。注意,以下字符从格式掩码按原样传递到输出:
/ - , . ; :
其他字符串可以通过括在引号中进行"传递":
SELECT TO_CHAR(SYSDATE, '"The time is now " HH24:MI:SS "precisely"') FROM dual
/* e.g. The time is now 17:38:22 precisely
您在该文档中将发现,还有很多格式掩码模式可用于满足各种使用情况。
注意: TO_CHAR 也可以与 TIMESTAMP 类型结合使用。
使用 TO_DATE。 Oracle 可以使用与 TO_CHAR 相同的格式掩码将字符串分析为 DATE 类型。 假设有一个字符串
20050726173102:
SELECT TO_DATE( '20050726173102', 'YYYYMMDDHH24MISS' ) FROM dual
或者,要转换"Jul 26, 2005 17:13:05",我可以使用:
SELECT TO_DATE('Jul 26, 2005 17:13:05', 'Mon DD, YYYY HH24:MI:SS') FROM dual
注意: 对于 TIMESTAMP 类型,等价函数为 TO_TIMESTAMP。
更改默认的日期格式。 默认情况下,Oracle 根据 NLS_DATE_FORMAT 参数中定义的格式掩码显示 DATE 类型。
可以在如下所示的会话中更改此默认日期格式:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
日期运算。 为确定 2005 年 7 月 26 日 6 天后的日期,只要将值 6 与 DATE 对象相加:
SELECT TO_DATE( '20050726173102', 'YYYYMMDDHH24MISS' ) + 6 FROM dual
/* e.g. 2005-08-01 17:13:05 */
此类操作的最小整数单位是一天。 为减去 18 个小时,需要日期的相应小数:
SELECT TO_DATE( '20050726173102', 'YYYYMMDDHH24MISS' ) - (1/24 * 18) FROM dual
/* e.g. 2005-07-25 23:31:02 */
同样,要加上 59 秒:
SELECT TO_DATE( '20050726173102', 'YYYYMMDDHH24MISS' )
+ (1/(24*60*60) * 59) FROM dual
在处理月份或年份时,由于两者均不能用常数天数表示(注意具有不同天数的闰年和闰月),因此需要 ADD_MONTHS 函数。 将 12 个月与某个日期相加:
SELECT ADD_MONTHS( TO_DATE( '20050726173102', 'YYYYMMDDHH24MISS' ),
12) FROM dual
/* e.g. 2006-07-26 17:31:02 */
注意: 要减去月数,请使用减号。
LAST_DAY 函数返回 DATE 类型的月份的最后一天:
SELECT LAST_DAY( TO_DATE( '20050701', 'YYYYMMDD' ) ) FROM dual
/* e.g. 2005-07-31 00:00:00 */
TRUNC 函数根据提供的日期掩码(即第二个参数),对 DATE 进行向下取整。 如果在进行 DATE
比较时需要从比较中消除秒和分钟这样的单位,可以使用该函数:
SELECT TRUNC( TO_DATE( '20050726173102', 'YYYYMMDDHH24MISS' ), 'DD' ) FROM dual
/* e.g. 2005-07-26 00:00:00 */
如果未提供日期掩码,TRUNC 将对 DATE 向下取整到它所表示的日的起始。
其他与日期相关的函数包括 MONTHS_BETWEEN(用于计算两个 DATE
类型之间的月数的整数差)、NEXT_DAY(用于获取与某个字符串文字(例如,"MONDAY")匹配的下一个星期几的 DATE 类型)以及
ROUND(类似于 TRUNC,但按就近取整原则返回最近 DATE)。
使用 Date 构建 SQL 语句
下面是一些在 SQL 语句中使用 DATE 类型,使用"emp"(员工)表(Oracle 中附带的示例数据的一部分)的示例。
"em"表的"hiredate"列使用 DATE 类型存储值。
找到在两个日期之间被辞退的所有员工:
SELECT
ename, TO_CHAR(hiredate, 'ddth Mon, YYYY')
FROM
emp
WHERE
hiredate
BETWEEN
TO_DATE('1980-01-01','YYYY-MM-DD')
AND
TO_DATE('1985-01-01','YYYY-MM-DD')
ORDER BY
hiredate
DESC
添加一个新员工:
INSERT INTO emp
(
empno,
ename,
job,
mgr,
hiredate,
sal,
deptno
)
VALUES
(
8002,
'JOHNSON',
'ANALYST',
7566,
TO_DATE('2005-07-22','YYYY-MM-DD'),
3000,
20
)
使用从 TO_YMINTERVAL 函数返回的 INTERVAL 类型找到所有在公司的工作时间超过 15 年的员工:
SELECT
*
FROM
emp
WHERE
SYSDATE - TO_YMINTERVAL('15-00') > hiredate
分享到:
相关推荐
ORACLE中日期和时间函数汇总,详细介绍了Oracle中的日期函数和时间函数的用法
Oracle时间日期操作
Oracle没有datetime类型,只有date类型,date类型可以同时用作日期时间类型。这里对Oracle日期时间类型做一下简单的介绍
里面有字符是否符时间期的两个函数,一个纯日期,还有一个加了时分秒。经测试可以用的
ORACLE 日期和时间处理汇总,各种日期时间函数,有实例供参考!
ORACLE日期时间函数大全
oracle中日期类型与unix 时间戳的转换, Unix时间戳记是从'1970-01-01 00:00:00'GMT开始的秒数,表现为整数型。 Oracle中的时间是Date型,以下函数提供了两种时间转换的Oracle函数 --unix时间戳与date时间互转 ...
Oracle_Database_11g_SQL日期和时间的存储与处理。
select sysdate from dual 等等一些关于oracle的查询当前时间,日期的一些sql语句的
oracle自定义日期函数、你值得拥有!
oracle 日期时间操作,oracle 日期时间
关于oracle的日期时间格式,各位亲了解多少呢,知道rrrr,fm等表示什么?这篇文档将想各位亲具体介绍oracle的各种日期格式,附带试例,赶快下载吧。
oracle关于日期时间 在oracle中处理日期大全
oracle日期时间函数大全,针对oracle的时间日期进行数据库查询。
Oracle服务器端日期时间格式设置修改
Oracle自动导出脚本(WINDOWS下的,导出文件名自动加上日期和时间)
1.日期时间间隔操作 当前时间减去7分钟的时间 select sysdate,sysdate - interval ’7’ MINUTE from dual 当前时间减去7小时的时间 select sysdate - interval ’7’ hour from dual 当前时间减去7...
简单的oracle 时间比较函数,自己在网上找到并加以整理出来。