RE: comparing datetime

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 19 Apr 2013 10:38:17 -0400
Message-ID: <018401ce3d0b$88d3ce80$9a7b6b80$_at_rsiz.com>

--> How can I change the filter used by oracle to compare datetimes?

Change your literal string into the type matching the column.

If you don't want to use a timestamp literal or the flexible to_timestamp functions, which have already been suggested, then you can cast your literal to match the column's type (time_stamp is a column of type timestamp(6) and the string matches the default NLS setting):

and cast('04-OCT-11 04.52.37.384000 PM' AS timestamp) = time_stamp

so you'll see in the filter something like:

filter("TIME_STAMP"=CAST('04-OCT-11 04.52.37.384000 PM' AS timestamp))

the important difference being that Oracle only has to operate the function CAST once on the literal value, while if you have a function on the column's value it has to operate on each row.
Further, if there happens to be an index in a viable position (leading, prefaced by other predicate columns) then the index might be used as a range scan or individual value lookup rather than just using the index as a cheaper source than the table of checking and filtering all values.

While there may be cases where Oracle can transform your query to use more efficient forms, it is of high value and clearer in meaning to write the statements so it is obvious to humans what your code is trying to do.

-----Original Message-----
<snip>

here the output:

plan_table_output

---

---

SQL_ID cv0jqbmyygwcw, child number 0

---

select data_invio_entrata from movimento_canina where data_invio_entrata='2012-03-13
14:12:14.476865' and rownum <= 1

Plan hash value: 3444997322

---

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |

---

---

| 0 | SELECT STATEMENT | | | | 2 (100)| | |
|* 1 | COUNT STOPKEY | | | | | | |
|* 2 | TABLE ACCESS FULL| MOVIMENTO_CANINA | 2 | 24 | 2 (0)| 00:00:01 |

VETER~ |

---

---

Predicate Information (identified by operation id):

---

1 - filter(ROWNUM<=1)
2 -
filter(SYS_EXTRACT_UTC("DATA_INVIO_ENTRATA")=SYS_EXTRACT_UTC(TO_TIMESTAMP_TZ ('201
2-03-13 14:12:14.476865')))

(22 rows)

---

---

It uses SYS_EXTRACT_UTC to compare values. I tried this:

SQL> select DATA_INVIO_ENTRATA from movimento_canina where SYS_EXTRACT_UTC(to_timestamp_tz('2012-03-13 14:12:14.476865')) = SYS_EXTRACT_UTC(DATA_INVIO_ENTRATA); data_invio_entrata

---

(0 rows)

SQL> select DATA_INVIO_ENTRATA from movimento_canina where SYS_EXTRACT_UTC(to_timestamp_tz('2012-03-13 14:12:14.476865')) = SYS_EXTRACT_UTC(to_timestamp_tz(DATA_INVIO_ENTRATA)) limit 1;

data_invio_entrata

---

2012-03-13 14:12:14.476865
(1 rows)

How can I change the filter used by oracle to compare datetimes?

desc:
name | type

--------------------------+ ---------------------------

id | number
data_invio_entrata | timestamp(6) with time zone

j

<snip>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Apr 19 2013 - 16:38:17 CEST