日期和时间的存储与处理

日期和时间的存储与处理

关于日期和时间在数据库中经常用到，下面总结了一些这方面的知识。
因为字符集对日期时间有影响，这个是我们首先要关注的，之前的文章中也简单的提到了（http://blog.itpub.net/29876893/viewspace-1627370/）
SQL> select * from nls_session_parameters where rownum<4;
PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_LANGUAGE
SIMPLIFIED CHINESE
NLS_TERRITORY
CHINA
NLS_CURRENCY
￥
此时该会话的字符集是SIMPLIFIED CHINESE，为了做实验，改成英文：

SQL> alter session set NLS_LANGUAGE=AMERICAN;
Session altered.


SQL> select * from nls_session_parameters where rownum<4;
PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_LANGUAGE
AMERICAN
NLS_TERRITORY
CHINA
NLS_CURRENCY
￥

如果读过之前写的文章，已经知道 Language:影响oracle消息,校验，日期命名


SQL> select sysdate from dual;

SYSDATE
------------------
09-MAY-15
默认存储在数据库中的日期是以：DD-MON-YYYY,读取默认是DD-MON-YY
我们可以使用关键字DATE，向数据库中插入日期：
SQL> create table t(t date);
Table created.

SQL> insert into t values('09-MAY-15');
1 row created.
SQL> insert into t values(DATE '1994-8-25');
1 row created.
SQL> insert into t values(DATE '1994-08-20');
1 row created.


SQL> select * from t;
T
------------------
09-MAY-15
25-AUG-94
20-AUG-94

但是使用DATE关键字，此时日期必须使用ANSI标准日期格式：YYYY-MM-DD


我们可以用函数to_char(x[,format]):把x转换成字符串，并用后面提供的格式，下面我们可以把日期转换成我们想要的格式：
SQL> select to_char(t,'DD-MON-YYYY') FROM t;
TO_CHAR(T,'DD-MON-YY
--------------------
09-MAY-2015
25-AUG-1994
20-AUG-1994
SQL> select to_char(t,'MON-DD-YYYY') FROM t;
TO_CHAR(T,'MON-DD-YY
--------------------
MAY-09-2015
AUG-25-1994
AUG-20-1994

SQL> select to_char(sysdate,'DD-MON-YYYY,HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DD-MON-YYYY,
-----------------------------
09-MAY-2015,23:58:54

上面只是举简单的例子，关于时间格式化参数可以参考文档。

to_date(x[,format])将x转换为DATE类型：

SQL> select to_date('MAY-6-2015') from dual;
select to_date('MAY-6-2015') from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

SQL> select to_date('5-MAY-2015') from dual;
TO_DATE('5-MAY-201
------------------
05-MAY-15
注意日期的格式DD-MON-YYYY(DD-MON-YY)所以上面的查询发生错误
SQL> select to_date('5-MAY-2015 9:50:45','DD-MON-YY HH24:MI:SS') FROM DUAL;
TO_DATE('5-MAY-201
------------------
05-MAY-15

SQL> select to_date('5-MAY-2015 9:50:45','DD-MON-YYYY HH24:MI:SS') FROM DUAL;
TO_DATE('5-MAY-201
------------------
05-MAY-15

SQL> select to_char(to_date('5-MAY-2015 9:50:45','DD-MON-YYYY HH24:MI:SS'),'MON-DD-YYYY HH24:MI:SS') FROM DUAL;
TO_CHAR(TO_DATE('5-M
--------------------
MAY-05-2015 09:50:45

SQL> select to_char(to_date('5-MAY-2015 9:50:45','DD-MM-YYYY HH24:MI:SS'),'MON-DD-YYYY HH24:MI:SS') FROM DUAL;
TO_CHAR(TO_DATE('5-MAY-20159:
-----------------------------
MAY-05-2015 09:50:45

SQL> select to_char(to_date('5-MAY-2015 9:50:45','DD-MM-YYYY HH24:MI:SS'),'MM-DD-YYYY HH24:MI:SS') FROM DUAL;
TO_CHAR(TO_DATE('5-
-------------------
05-05-2015 09:50:45

可以结合to_char和to_date，可以使用不同格式的时间值。
我们通过设置nls_date_format,指定日期格式，如果是全局设置，在参数文件中设置，也可以在session中设置啦：

SQL> show parameter nls_date


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_date_format string
nls_date_language string


SQL> show user;
USER is "HR"
SQL> alter session set nls_date_format='MON-DD-YYYY';
Session altered.

SQL> select sysdate from dual;
SYSDATE
--------------------
MAY-10-2015


oracle虽然存储了4位年份，但是如果只提供了2位数字，oracle就会根据所使用的格式是YY还是RR来解释世纪：

如果日期中的年份是YY格式，并且只提供了两位年份，那么久认为这一年的世纪和数据服务器上的当前世纪相同，这不必多说。

如果日期中采用的是RR格式，并且只提供了后两位，那么年份中的前两位由两部分决定：
提供年份的两位数字（指定年），数据库服务器上当前日期中年份的后两位数字，规则如下：（设服务器当前日期前两位为F2，指定日期前两位为Z2)
Z2 in (00 ~ 49) 　Ｆ２　ＩＮ（００～４９）　　同世纪　Ｆ２
Ｚ２　ＩＮ（５０　～９９）　　　Ｆ２　ＩＮ（００　～　４９）　　　Ｆ２－１
Ｚ２　ＩＮ（０～４９）　　Ｆ２　ＩＮ（５０～９９）　　　Ｆ２＋１
Ｚ２　ＩＮ（５０～９９）　　Ｆ２　ＩＮ（５０～９９）　Ｆ２


SQL> select to_char(to_date('05-MAY-15','DD-MM-RR'),'DD-MM-YYYY') FROM DUAL;

TO_CHAR(TO
----------
05-05-2015

SQL> select to_char(to_date('05-MAY-65','DD-MM-RR'),'DD-MM-YYYY') FROM DUAL;


TO_CHAR(TO
----------
05-05-1965


就先总结到这里吧！