oracle如何查询当天的数据!

[yangzong20]

在test表里有一个dadate日期字段里面的格式如下


id dadate
1 2011-3-23 10:23:45
2 2011-2-21 12:34:21
3 2011-3-12 6:23:12


我想按dadate 查询当天的记录如何查询？望高手告知，谢谢

[UPC子夜]

select * from test where to_char(dadate,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')

[Megan2008]

select * from test where dadate>=trunc(sysdate) and dadate<trunc(sysdate)+1

[luoyoumou]

-- 查询一表中昨天生成的数据
-- 原表mobilefrends中的cdate字段上有索引，创建索引语句是：create index mobilefrends_cdate_idx on mobilefrends(cdate);

---------------------------------------------------------------------------------------------------------------------
-- 方法一：用to_char()函数

hll@SZTYORA> select count(*) from mobilefrends where to_char(cdate,'yyyy-mm-dd')=to_char(sysdate-1,'yyyy-mm-dd');

  COUNT(*)
----------
     82119

已用时间:  00: 00: 17.18

执行计划
----------------------------------------------------------
Plan hash value: 3731074549

------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                        |     1 |     8 | 11773  (16)| 00:02:22 |
|   1 |  SORT AGGREGATE       |                        |     1 |     8 |            |          |
|*  2 |   INDEX FAST FULL SCAN| MOBILEFRENDS_CDATE_IDX |   129K|  1012K| 11773  (16)| 00:02:22 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_CHAR(INTERNAL_FUNCTION("CDATE"),'yyyy-mm-dd')=TO_CHAR(SYSDATE@!-1,'yyyy
              -mm-dd'))


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      36896  consistent gets
          0  physical reads
          0  redo size
        345  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

---------------------------------------------------------------------------------------------------------------------
-- 方法二：用trunc()函数

hll@SZTYORA> select count(*) from mobilefrends where trunc(cdate)=trunc(sysdate-1);

  COUNT(*)
----------
     82119

已用时间:  00: 00: 16.32

执行计划
----------------------------------------------------------
Plan hash value: 3731074549

------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                        |     1 |     8 | 12580  (22)| 00:02:31 |
|   1 |  SORT AGGREGATE       |                        |     1 |     8 |            |          |
|*  2 |   INDEX FAST FULL SCAN| MOBILEFRENDS_CDATE_IDX |   129K|  1012K| 12580  (22)| 00:02:31 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TRUNC(INTERNAL_FUNCTION("CDATE"))=TRUNC(SYSDATE@!-1))


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      36896  consistent gets
          0  physical reads
          0  redo size
        345  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

---------------------------------------------------------------------------------------------------------------------
-- 方法三：不用函数
hll@SZTYORA> select count(*) from mobilefrends where cdate>=trunc(sysdate-1) and cdate<trunc(sysdate);

  COUNT(*)
----------
     82119

已用时间:  00: 00: 00.43

执行计划
----------------------------------------------------------
Plan hash value: 2668176725

---------------------------------------------------------------------------------------------
| Id  | Operation          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                        |     1 |     8 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |                        |     1 |     8 |            |          |
|*  2 |   FILTER           |                        |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| MOBILEFRENDS_CDATE_IDX |    48 |   384 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TRUNC(SYSDATE@!-1)<TRUNC(SYSDATE@!))
   3 - access("CDATE">=TRUNC(SYSDATE@!-1) AND "CDATE"<TRUNC(SYSDATE@!))


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        221  consistent gets
          0  physical reads
          0  redo size
        345  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

---------------------------------------------------------------------------------------------------------------------


-- 由上三种方法可见：
-- 方法一：用to_char()函数，耗时17.18秒，逻辑读为36896次，执行的是全索引扫描；
-- 方法二：用trunc()函数，耗时16.32秒，逻辑读也是36896次，执行的也是全索引扫描；
-- 方法三：不用函数，耗时0.43秒，逻辑读为221次，执行的是索引范围扫描；


-- 请问：哪种方法好呢？

[newdiscover]

[Quote=引用 4 楼 luoyoumou 的回复:]
SQL code
-- 查询一表中昨天生成的数据
-- 原表mobilefrends中的cdate字段上有索引，创建索引语句是：create index mobilefrends_cdate_idx on mobilefrends(cdate);

-----------------------------------------------------------------------……
[/Quote]

佩服！

[changhe325]

楼主看下 有关oracle时间的函数吧。

[yinan9]

select * from test where trunc(dadate)=trunc(sysdate)

[超超王贱贱]

select * from test where trunc(dadate)=trunc(sysdate)

[liujt17]

select * from test where trunc(dadate)=trunc(sysdate)

[show_love]

select * from test t where trunc(sysdate)=trunc(datate);

[xuyazhou]

good

[xuyazhou]

[Quote=引用 7 楼 yinan9 的回复:]
SQL code
select * from test where trunc(dadate)=trunc(sysdate)
[/Quote]
对的