oracle日期时间数据类型,Oracle 内置数据类型 -- 日期时间

-- Start

DATE

DATE 由以下部分组成。

组成部分 默认值

year 当前年

month 当前月

day 01

hour 0

minute 0

second 0

怎么会有默认值呢？看了下面你就知道了。

CREATE TABLE TEST

(

COL DATE

);

-- 语法1：日期必须是环境变量 NLS_DATE_FORMAT 指定的格式

-- 查询：SELECT * FROM NLS_SESSION_PARAMETERS WHERE PARAMETER='NLS_DATE_FORMAT';

INSERT INTO TEST VALUES ('13-12月-14');

-- 语法2：DATE 'YYYY-MM-DD'

INSERT INTO TEST VALUES (DATE '2014-12-13');

-- 语法3：TO_DATE 方法

INSERT INTO TEST VALUES (TO_DATE('2014-12-13', 'YYYY-MM-DD'));

INSERT INTO TEST VALUES (TO_DATE('2014-12-13 20:30:18', 'YYYY-MM-DD HH24:MI:SS'));

-- SYSDATE 返回当前系统时间

INSERT INTO TEST VALUES (SYSDATE);

-- CURRENT_DATE 返回当前会话时区的时间

INSERT INTO TEST VALUES (CURRENT_DATE);

-- 变态的 Oracle 颠覆了我们的观念，DATE 怎么能包含时分秒呢？这将给查询带来问题

SELECT * FROM TEST WHERE COL = DATE '2014-12-13';

-- 为了确保查询没有问题，我们不得不把 DATE 条件转成范围扫描

SELECT * FROM TEST WHERE COL >= DATE '2014-12-13' AND COL < DATE '2014-12-14';

-- 我们也可以使用 TRUNC 去除时，分，秒，不过这将使索引失效

SELECT * FROM TEST WHERE TRUNC(COL) = DATE '2014-12-13';

TIMESTAMP

CREATE TABLE TEST

(

-- 语法：TIMESTAMP [(fractional_seconds_precision)]

-- fractional_seconds_precision 可以是 0 到 9 的任意值，默认值是 6

COL1 TIMESTAMP,

COL2 TIMESTAMP(3)

);

-- 语法1：日期必须是环境变量 NLS_TIMESTAMP_FORMAT 指定的格式

-- 查询：SELECT * FROM NLS_SESSION_PARAMETERS WHERE PARAMETER='NLS_TIMESTAMP_FORMAT';

INSERT INTO TEST VALUES ('13-12月-14 06.05.36.985211 下午', '13-12月-14 06.05.36.211 上午');

-- 语法2：TIMESTAMP 'YYYY-MM-DD HH:MI:SS.FF[1...9]'

INSERT INTO TEST VALUES (TIMESTAMP '2014-12-13 18:01:50.123456', TIMESTAMP '2014-12-13 18:01:50.123');

-- 语法3：TO_TIMESTAMP 方法

INSERT INTO TEST VALUES (TO_TIMESTAMP('2014-12-13', 'YYYY-MM-DD')， TO_TIMESTAMP('2014-12-13 20:30:18.123', 'YYYY-MM-DD HH24:MI:SS.FF3'));

-- SYSTIMESTAMP 返回当前系统时间

INSERT INTO TEST VALUES (SYSTIMESTAMP, SYSTIMESTAMP);

-- CURRENT_TIMESTAMP 返回当前会话时区的时间

INSERT INTO TEST VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

TIMESTAMP WITH TIME ZONE

CREATE TABLE TEST

(

-- 语法：TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

-- fractional_seconds_precision 可以是 0 到 9 的任意值，默认值是 6

COL1 TIMESTAMP WITH TIME ZONE,

COL2 TIMESTAMP(3) WITH TIME ZONE

);

-- 语法1：日期必须是环境变量 NLS_TIMESTAMP_TZ_FORMAT 指定的格式

-- 查询：SELECT * FROM NLS_SESSION_PARAMETERS WHERE PARAMETER='NLS_TIMESTAMP_TZ_FORMAT';

INSERT INTO TEST VALUES ('13-12月-14 06.05.36.985211 下午 +08:00', '13-12月-14 06.05.36.211 上午 -05:00');

-- 语法2：TIMESTAMP 'YYYY-MM-DD HH:MI:SS.FF[1...9] TZH:TZM'

INSERT INTO TEST VALUES (TIMESTAMP '2014-12-13 18:01:50.123456 +09:00', TIMESTAMP '2014-12-13 18:01:50.123 -05:00');

-- 语法3：TIMESTAMP 'YYYY-MM-DD HH:MI:SS.FF[1...9] TZR'

-- 查询 TZR: SELECT TZNAME FROM V$TIMEZONE_NAMES

INSERT INTO TEST VALUES (TIMESTAMP '2014-12-13 18:01:50.123456 Asia/Shanghai', TIMESTAMP '2014-12-13 18:01:50.123 Asia/Tokyo');

-- 语法4：TIMESTAMP 'YYYY-MM-DD HH:MI:SS.FF[1...9]' AT TIME ZONE 'TZR'

INSERT INTO TEST VALUES (TIMESTAMP '2014-12-13 18:01:50.123456' AT TIME ZONE 'Asia/Shanghai', TIMESTAMP '2014-12-13 18:01:50.123456' AT TIME ZONE 'Asia/Tokyo');

-- 语法5：TO_TIMESTAMP_TZ 方法

INSERT INTO TEST VALUES (TO_TIMESTAMP_TZ('2014-12-13 20:30:18 +08:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM'), TO_TIMESTAMP_TZ('2014-12-13 20:30:18 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'));

TIMESTAMP WITH LOCAL TIME ZONE

CREATE TABLE TEST

(

-- 语法：TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE

-- 事实上在数据库中它并不保存时区信息，而是保存成数据库时区的时间

-- 查询时，自动转成 session 的时区

-- fractional_seconds_precision 可以是 0 到 9 的任意值，默认值是 6

COL1 TIMESTAMP WITH LOCAL TIME ZONE,

COL2 TIMESTAMP(3) WITH LOCAL TIME ZONE

);

-- 以上介绍的方式都可以用在 TIMESTAMP WITH LOCAL TIME ZONE

INSERT INTO TEST VALUES ('13-12月-14', DATE '2014-12-13');

INSERT INTO TEST VALUES (TO_DATE('2014-12-13', 'YYYY-MM-DD'), TO_DATE('2014-12-13 20:30:18', 'YYYY-MM-DD HH24:MI:SS'));

INSERT INTO TEST VALUES (SYSDATE, CURRENT_DATE);

INTERVAL YEAR TO MONTH

CREATE TABLE TEST

(

-- 它表示一个时间间隔，一般我们很少使用它建表，它主要用在日期的加减运算中

-- 语法：INTERVAL YEAR [(year_precision)] TO MONTH

-- year_precision　表示年的精度, 可以是 0 到 9 的任意值, 默认值是　2

COL1 INTERVAL YEAR TO MONTH,

COL2 INTERVAL YEAR(3) TO MONTH

);

INSERT INTO TEST VALUES (INTERVAL '99' YEAR, INTERVAL '99' YEAR(2));

INSERT INTO TEST VALUES (INTERVAL '1199' MONTH, INTERVAL '1200' MONTH(4));

INSERT INTO TEST VALUES (INTERVAL '10-2' YEAR TO MONTH, INTERVAL '10-2' YEAR(2) TO MONTH);

INTERVAL DAY TO SECOND

CREATE TABLE TEST

(

-- 它表示一个时间间隔，一般我们很少使用它建表，它主要用在日期的加减运算中

-- 语法：INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]

-- day_precision 表示日的精度，值可以是 0 到　9， 默认值是　2

-- fractional_seconds_precision 表示秒的小数位数，值可以是 0 到　9， 默认值是　6

COL1 INTERVAL DAY TO SECOND,

COL2 INTERVAL DAY(3) TO SECOND(9)

);

INSERT INTO TEST VALUES (INTERVAL '15' DAY, INTERVAL '15' DAY(2));

INSERT INTO TEST VALUES (INTERVAL '15' HOUR, INTERVAL '15' MINUTE);

INSERT INTO TEST VALUES (INTERVAL '15' SECOND, INTERVAL '15.1515' SECOND);

INSERT INTO TEST VALUES (INTERVAL '15 5' DAY TO HOUR, INTERVAL '15 5:5' DAY TO MINUTE);

INSERT INTO TEST VALUES (INTERVAL '4 5:5:5' DAY TO SECOND, INTERVAL '4 5:5:5.555' DAY TO SECOND);

INSERT INTO TEST VALUES (INTERVAL '5' HOUR, INTERVAL '5:5' HOUR TO MINUTE);

INSERT INTO TEST VALUES (INTERVAL '5:5:5' HOUR TO SECOND, INTERVAL '5:5:5.555' HOUR TO SECOND);

INSERT INTO TEST VALUES (INTERVAL '10:22' MINUTE TO SECOND, INTERVAL '10:22.222' MINUTE TO SECOND);

--

更多参见：Oracle SQL 精萃

-- 声明：转载请注明出处

-- Last edited on 2015-07-27

-- Created by ShangBo on 2014-12-13

-- End