ORACLE中DATE自动转化为TIMESTAMP

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

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

1、构建数据

show parameter nls_date_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string      YYYY-MM-DD

DROP TABLE t PURGE;
CREATE TABLE t
AS
SELECT (SYSDATE + LEVEL) AS t_date
FROM dual
CONNECT BY LEVEL < 10;

CREATE INDEX idx_t ON t(t_date);

SELECT * FROM t;
T_DATE
----------
2014-11-28
2014-11-29
2014-11-30
2014-12-01
2014-12-02
2014-12-03
2014-12-04
2014-12-05
2014-12-06

SELECT 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 EXPLAIN
SELECT * FROM t WHERE t_date=TO_DATE('2014-11-28', 'yyyy-mm-dd');

no rows selected

Execution 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') AND
	t_date < TO_DATE('2014-11-29', 'yyyy-mm-dd');

T_DATE
----------
2014-11-28

Execution 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)

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