ORACLE中DATE自动转化为TIMESTAMP

来源：互联网发布：手机变声软件女变男编辑：程序博客网时间：2024/06/07 02:00

今天在写SELECT语句的时候发现始终差不出来等于某个日期的数据，后来才发现是ORACLE内部发生的转化。

废话不多说，直接看示例。

1、构建数据

show parameter nls_date_formatNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------nls_date_format                      string      YYYY-MM-DDDROP TABLE t PURGE;CREATE TABLE tASSELECT (SYSDATE + LEVEL) AS t_dateFROM dualCONNECT BY LEVEL < 10;CREATE INDEX idx_t ON t(t_date);SELECT * FROM t;T_DATE----------2014-11-282014-11-292014-11-302014-12-012014-12-022014-12-032014-12-042014-12-052014-12-06SELECT table_name, column_name, data_type FROM USER_TAB_COLUMNS WHERE table_name = 'T';TABLE_NAME                     COLUMN_NAME                    DATA_TYPE------------------------------ ------------------------------ --------------------T                              T_DATE                         DATE

由上面可以看出创建了一个表T，其中有一个类型为DATE的T_DATE字段

2、困扰怎么没有数据出来

SELECT * FROM t WHERE t_date='2014-11-28';no rows selected

难道是没有将2014-11-28转换成DATE类型的原因？

SELECT * FROM t WHERE t_date=TO_DATE('2014-11-28', 'yyyy-mm-dd');no rows selected

还是没有，真奇怪！
3、像碰到这样的问题，我首先想到的是查看执行计划

以上两种查询方法建议使用第二种，它使用了显示类型转化，更具有可读性，等让人明白。

SET AUTOTRACE ON EXPLAINSELECT * FROM t WHERE t_date=TO_DATE('2014-11-28', 'yyyy-mm-dd');no rows selectedExecution Plan----------------------------------------------------------Plan hash value: 2296882198--------------------------------------------------------------------------| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT |       |     1 |     9 |     1   (0)| 00:00:01 ||*  1 |  INDEX RANGE SCAN| IDX_T |     1 |     9 |     1   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("T_DATE"=TO_DATE(' 2014-11-28 00:00:00', 'syyyy-mm-dd              hh24:mi:ss'))Note-----   - dynamic sampling used for this statement (level=2)

通过上面的执行计划，可以看到ORACLE自动发生了DATE -> TIMESTAMP的转化：
"T_DATE"=TO_DATE(' 2014-11-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss')

4、所以我们要查询出相关时间只能是另寻他法了。

SELECT * FROM t WHERE t_date >= TO_DATE('2014-11-28', 'yyyy-mm-dd') ANDt_date < TO_DATE('2014-11-29', 'yyyy-mm-dd');T_DATE----------2014-11-28Execution Plan----------------------------------------------------------Plan hash value: 2296882198--------------------------------------------------------------------------| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT |       |     1 |     9 |     1   (0)| 00:00:01 ||*  1 |  INDEX RANGE SCAN| IDX_T |     1 |     9 |     1   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("T_DATE">=TO_DATE(' 2014-11-28 00:00:00', 'syyyy-mm-dd              hh24:mi:ss') AND "T_DATE"<TO_DATE(' 2014-11-29 00:00:00', 'syyyy-mm-dd              hh24:mi:ss'))Note-----   - dynamic sampling used for this statement (level=2)

由上看到数据终于查出来了

0 0