关于date格式的两个案例(r4笔记第96天)

在工作中总是会碰到各种和date相关的问题,一般这种问题都是让人很纠结的。 比如前几天一个朋友和我分享了他关于时间问题的两个案例。 第一个是他在做impdp导入数据的时候，发现导入很顺利完成了，但是原本的timestamp数据类型的数据都变成了sysdate值了，这样数据就完全不对应了。timestamp的数据类型精度要高得多，可以精确到小数秒（默认是6位，算是微妙了，可以最高到9位），可能在有些系统中精确到秒已经足够了，但是据我所知，还没有这种数据类型的强制转换。我们聊了会，基本的共识就是数据库层面不会自动做这种数据类型的转换，过了会他告诉我，发现问题了，是由于触发器导致的。 触发器的内容大体如下：

CREATE OR REPLACE TRIGGER "XXXXXX_TRG" BEFORE INSERT OR UPDATE ON XXXXXX_HISTORY REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN IF (INSERTING) THEN :NEW.ADD_TIMESTAMP := SYSTIMESTAMP; END IF; :NEW.REC_TIMESTAMP := SYSTIMESTAMP; END;

有了这些信息，也算是虚惊一场。 不过触发器带来的问题真是无所不在，我之前在数据迁移的时候碰到一个问题，也是在数据导入的时候没有禁用触发器，结果数据导入之后，本来需要导入1500万数据的表，结果在数据导入之后发现导入了1800万，多了300万的数据，当时也是感觉很蹊跷，最后排查发现是由于触发器导致的。最后也是经过反复确认，准备了一堆的脚本最终清理了那多余的300万数据。也算是沉痛的教训，所以在数据导入的过程中，触发器的作用是显而易见的，有时候我们需要禁用，有时候需要启用，得看业务需要了。 过了几天，这个朋友碰到了另外一个问题，问题的场景更为复杂，是一个日本开发的系统做迁移(包括应用代码迁移),通过客户端程序调用的时候发生了数据类型的冲突。 朋友在他们的应用程序中的代码查到他们的SQL代码。

如下格式: select * from emp where hiredate >= to_char(sysdate,'YYYY-MM-DD'); 他们在sql中用date 型数据与 char型的数据做了一个比较。 大家都知道，这种写法是有错误的，一般会报一个 ORA-01861: 文字与格式字符串不匹配 但是这生产环境已经运行了很长时间，一直没有报错，原因是为什么呢？ 大家都知道 NLS_DATE_FORMAT这个参数，也知道有LANG这个环境变量 也知道session>instance>database; 但是是否有想过session中这个 NLS_DATE_FORMAT 是根据什么去判定的？是session 发起端的 date_format，还是 db端响应的date_foramt. date_format 与语言环境又有没有关系？ 一般来说能够修改NLS_DATE_FORMAT的方式有以下几种 第一种是通过系统变量NLS_DATE_FORMAT,这个变量也是依赖于NLS_LANG的设置的。 第二种是通过sqlplus的glogin.sql来设置NLS_DATE_FORMAT，alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';这个也算是统一的配置了。

第三种是通过session级的变更来完成，直接作用于当前的session. alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

第四钟是通过system级的变更，直接通过alter system set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';这个作用范围很广，一般是不需要这么做的。 这几种方式貌似都和他碰到的问题不搭边，最后他是通过logon触发器来搞定的。可以设定一些规则，做一些针对性的操作。 触发器的内容如下： CREATE OR REPLACE TRIGGER LOGINTRG AFTER LOGON ON DATABASE BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''yyyy-mm-dd hh24:mi:ss'''; END LOGINTRG; 这种方式确实是奏效了，但是解决了问题但是心里还是感觉不够踏实，因为这种一种特殊的处理。 最后他们经过很多的分析，原本的设置是英文，发现修改客户端的LANG参数配置为日文就不需要触发器了，这个问题就引刃而解了。 我们先从LANG这个环境变量入手 大家都知道 LANG变量是language的简称，比如这几种配置 LANG=zh_CN.UTF-8 LANG=en_US.UTF-8 LANG=ja_JP.UTF-8 下面是三个语言环境的日期格式 [oracle@iZ23snm97y9Z ~]$ export LANG=zh_CN.UTF-8 [oracle@iZ23snm97y9Z ~]$ date 2015年 04月 01日 星期三 11:14:50 CST [oracle@iZ23snm97y9Z ~]$ export LANG=ja_JP.UTF-8 [oracle@iZ23snm97y9Z ~]$ date 2015年 4月 1日 水曜日 11:15:04 CST [oracle@iZ23snm97y9Z ~]$ export LANG=en_US.UTF-8 [oracle@iZ23snm97y9Z ~]$ date Wed Apr 1 11:15:23 CST 2015 LANG是针对Linux系统的语言、地区、字符集的设置，对linux下的应用程序有效，如date；NLS_LANG是针对Oracle语言、地区、字符集的设置，对oracle中的工具有效 这个问题的模拟，还是需要模拟客户端程序来做了，关于日期的一些格式化设置，目前还是需要通过分析一下JDBC中的一些细节来看看有什么细节之处了。

最后还是感谢木呼提供的精彩案例。