1. 时间值：可以使用DATE类型存储，DATE类型可存储世纪、4位年份、月、日、时（以24小时的格式）、分和秒

时间戳：与DATE相比，时间戳可以存储带有小数位的秒，还可以存储时区。

时间间隔：存储时间的长度。

2. 默认情况下，日期以DD-MON-YYYY格式保存到数据库中。可以使用DATE关键字提供一个日期字面量，此时日期必须使用ANSI标准日期格式YYYY-MM-DD。默认情况数据库以DD-MON-YY格式返回日期。

例：INSERT INTO customers

(customer_id,first_name,last_name,dob,phone）

           VALUES(
                          7,'Steve','Purple',DATE '1972-10-25','800-555-1215'
                           );

3. 使用TO_CHAR和TO_DATE转换时间值

1） TO_CHAR(x [,format])：将时间值转换为字符串

例：SELECT TO_CHAR(SYSDATE,'MONTH DD,YYYY,HH24:MI:SS') FROM dual;

2） TO_DATE(x [,format])：将字符串转换为时间值

例：SELECT TO_DATE('July 4,2007','MONTH DD,YYYY') FROM dual;

注：在中文环境的客户端使用TO_DATE时，可能会出现错误：Runtime error occurred: 1843 (ORA-01843: 无效的月份)，解决办法：alter session nls_date_language='america';

如果还是出错，则断开连接，输入命令：set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

4. 设置默认的日期格式

例：ALTER SESSION SET NLS_DATE_FORMAT = 'MOTH-DD-YYYY';

5. oracle对2位年份的处理

1）使用YY格式

规则：若日期中的年份采用YY或YYYY，但只提供2位的年份，就认为这一年的世纪与数据库服务器上当前设置的世纪相同。

例：若果提供的年份是15，而当年是2007年，则提供的年份就是2015年。

2）使用RR格式

规则：1. 如果指定年在00-49之间，且当前年份在00-49之间，则指定年份与当前年份的世纪相同。

2.如果指定年在50-99之间，且当前年份在00-49之间，则指定年份的世纪等于当前年份的世纪减去1。

3.如果指定年在00-49之间，且当前年份在50-99之间，则指定年份的世纪等于当前年份的世纪加上1。

4.如果指定年在50-99之间，且当前年份在50-99之间，则指定年份与当前年份的世纪相同。

6. 使用时间值函数

1） ADD_MONTHS(x,y)：返回x加上y个月的结果。如果y是负数，就减去y个月。

2） LAST_DAY(x)：返回包含x的月的最后一天。

例：SELECT LAST_DAY('01-JAN-2008') FROM dual; --31-JAN-08

3） MONTHS_BETWEEN(x,y)：返回x和y之间有几个月。如果x比y早，则返回负数。

4） NEXT_DAY(x,day)：返回从x开始，下一个day的日期。day是一个文本字符串，例如SATURDAY。

例：SELECT NEXT_DAY('02-JUN-2009','SATURDAY') FROM dual; --06-JUN-09

5） ROUND(x [x,unit])：对x取整，默认为最近的一天。

6） SYSDATE：返回数据库服务器的操作系统总设置的当前时间值。

7） TRUNC(x [,unit])：对x截断，默认被截断为当天的开始时间。unit指名要截断的单元。

7. 时区

1）定义：时区是当地时间与英格兰格林威治时间的时差。格林威治时间现在被称为协调世界时间（Coordinated Universal Time,UTC）。时区可以使用与UTC的时差（offset）或地理区域表示：

例：-08：00和+02：15分别显示的时差是UTC之后8小时和UTC之前2小时15分。

PST表示太平洋标准时间，它比UTC晚8个小时。

2）数据库时区和会话时区

1. 数据库时区由数据库参数TIME_ZONE控制，DBA可以在数据库的init.ora或spfile.ora文件中修改TIME_ZONE参数的设置，或使用ALTER DATABASE SET TIME_ZONE=offset | region设置。DBTIMEZONE函数可用于查看数据库的时区。

2. 默认情况下，会话时区与操作系统时区相同。可以使用ATLER SESSION SET TIME_ZONE=offset | region设置。SESSIONTIMEZONE函数可用于查看会话时区。CURRENT_DATE函数可查看会话时区中的日期。

3） TZ_OFFSET(time_zone)：获取时区的时差。

可通过检索v$timezone_names获取时区名。

NEW_TIME(x,time_zone1,time_zone2)：将x从time_zone1转换为time_zone2，并返回新的时间值。

8. 时间戳

1）时间戳类型

1. TIMESTAMP[(seconds_precision)]

存储世纪、4位年份、月、日、时（以24小时的格式）、分和秒。seconds_precision用于指定精度。范围从0-9，默认为9.表示秒的数字的小数点右边可存储seconds_precision位数字。超过，则取整。

2. TIMESTAMP[(seconds_precision)] WITH TIME ZONE

扩展TIMESTAMP，用于存储时区

3. TIMESTAMP[(seconds_precision)] WITH LOCAL TIME ZONE

扩展TIMESTAMP，将给定的时间值转换成数据库的本地时区。

2）相关函数

1. CURRENT_TIMESTAMP()：返回一个TIMESTAMP WITH TIME ZONE类型的值，其中包括当前会话的日期和时间以及会话的时区。

2. EXTRACT({YEAR | MONTH | DAY| HOUR | MINUTE | SECOND} | {TIMEZONE_HOUR | TIMEZONE_MINUTE} | {TIMEZONE_REGION | TIMEZONE_ABBR} FROM x)

从x中提取并返回年、月、日、时、分、秒或时区，其中x可以是时间戳类型或DATE类型。

3. FROM_TZ(x,time_zone)：将TIMESTAMP类型的x转换为由time_zone指定的时区，并返回TIMESTAMP WITH TIME ZONE类型。time_zone必须被指定为+|-HH:MI格式的字符串。

4. LOCALTIMESTAMP：返回一个TIMESTAMP类型，其中包括会话的当前日前和时间。

5. SYSTIMESTAMP：返回一个TIMESTAMP WITH TIME ZONE类型，其中包括数据库的当前日期、时间以及数据库时区。

6. SYS_EXTRACT_UTC(x)：将TIMESTAMP WITH TIME ZONE类型的x转换为一个TIMESTAMP类型，其中包括了UTC时区中的日期和时间。

7. TO_TIMESTAMP(x [,format])：将字符串x转换为一个TIMESTAMP类型。

8. TO_TIMESTAMP_TZ(x [,format])：将字符串x转换为一个TIMESTAMP WITH TIME ZONE类型。

9. CAST(x AS TIMESTAMP WITH LOCAL TIME ZONE)：将字符串x转换为TIMESTAMP WITH LOCAL TIME ZONE。

9. 时间间隔

1）类型：

1. INTERVAL YEAR[(years_precision)] TO MONTH

存储一个时间间隔，单位为年和月，可选的years_precison指定年的精度，范围为0-9，默认为2.若给出的年数超过精度，则返回错误。

2. INTERVAL DAY[(days_precision)] TO SECOND[(seconds_precision)]

存储一个时间间隔，单位为天和秒，可选的days_precision指定天的精度，范围为0-9，默认为2.可选的seconds_precision指定秒的小数部分的精度，范围为0-9，默认为6.

2）使用下面语法向数据库提供INTERVAL YEAR TO MONTH字面值：

INTERVAL '[+ | -] [y] [-m]' [YEAR [(years_precision)]] [TO MONTH]

例：1. INTERVAL '1' YEAR --时间间隔为1年

2. INTERVAL '11' MONTH --时间间隔为11个月

3. INTERVAL '1-3' YEAR TO MONTH --时间间隔为1年零3个月

4. INTERVAL -1-5 YEAR TO MONTH --时间间隔为负数，值为1年零5个月

3）使用下面语法向数据库提供INTERVAL DAY TO SECOND字面值:

INTERVAL ''[+ | -] [d] [h [: m [: s]]]' [DAY [(days_precision)]] [TO HOUR | MINUTE | SECOND [(seconds_precision)]]

例： 1. INTERVAL '3' DAY --时间间隔为3天

2. INTERVAL '45' SECOND --时间间隔为45秒

3. INTERVAL '3 2' DAY TO HOUR --时间间隔为3天零2小时

4. INTERVAL '3 2:25:45' DAY TO SECOND --世家间隔为3天零2小时25分45秒

4）相关函数

1. NUMTODSINTERVAL(x,interval_unit)

将数字x转换为一个INTERVAL DAY TO SECOND类型，interval_unit可设置为DAY、HOUR、MINUTE或SECOND

            例：SELECT NUMTODSINTERVAL (1.5,'DAY')
                                 ,NUMTODSINTERVAL (3.25,'HOUR')
                                   ,NUMTODSINTERVAL (10.123456789,'SECOND')
                   FROM dual;

NUMTODSINTERVAL(1.5,'DAY')
---------------------------------------------------------------------------
NUMTODSINTERVAL(3.25,'HOUR')
---------------------------------------------------------------------------
NUMTODSINTERVAL(10.123456789,'SECOND')
---------------------------------------------------------------------------
+000000001 12:00:00.000000000
+000000000 03:15:00.000000000
+000000000 00:00:10.123456789

2. NUMTOYMINTERVAL(x,interval_unit)

将数字x转换为一个INTERVAL YEARTO MONTH类型，interval_unit可设置为YEAR或MONTH

3. TO_DSINTERVAL

将字符串x转换为一个INTERVAL DAY TO SECOND类型

4. TO_YMINTERVAL

将字符串x转换为一个INTERVAL YEAR TO DAY类型

ITPub博客

日期和时间的存储与管理

最新文章