RE: comparing datetime
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <jose.soares@xxxxxxxxxxxxxx>, "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 19 Apr 2013 10:38:17 -0400
--> 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>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
