Re: comparing datetime
Date: Fri, 19 Apr 2013 09:11:17 +0200
Message-ID: <5170EE15.10300_at_sferacarta.com>
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
Mark W. Farnham wrote:
> What you're showing is the plan for display_cursor itself. I suspect you
> were debugging it to work in your release.
>
> Run the query with the timestamp, then run the display_cursor. In 10 the
> default is the previously executed query, so it should work for you.
> A filter line should show up in the query that shows you how the predicate
> is interpreted into an explicit representation from your implicit string
> conversion.
>
> I don't have a good portable solution for you unless the literal timestamp
> format the other fellow in the thread suggested works for all the databases
> you need to hit.
> Using the cast function (or any function) against the table's column will
> have the effect of running the function on each row returned. If you can
> cast the literal as a timestamp to match the column the literal only has to
> be handled once.
>
> good luck,
>
> mwf
>
> -----Original Message-----
> From: jo [mailto:jose.soares_at_sferacarta.com]
> Sent: Thursday, April 18, 2013 6:09 AM
> To: mwf_at_rsiz.com
> Subject: Re: comparing datetime
>
> Hi Mark,
>
> I changed your query as:
>
> select * from table(dbms_xplan.display_cursor);
>
> and there is thi output...could you see anything helpful?
>
>
> plan_table_output
> ----------------------------------------------------------------------------
> -
> SQL_ID dt0hqkzcdq5ny, child number 0
> -------------------------------------
> select * from table(dbms_xplan.display_cursor)
>
> Plan hash value: 3713220770
>
> ----------------------------------------------------------------------------
> -
> | Id | Operation | Name | Cost | Inst |
> ----------------------------------------------------------------------------
> -
> | 0 | SELECT STATEMENT | | 24 | |
> | 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR | | VETER~ |
> ----------------------------------------------------------------------------
> -
>
> Note
> -----
> - cpu costing is off (consider enabling it)
>
> (17 rows)
>
>
> Mark W. Farnham wrote:
>
>> You really don't want to run the function on the column for each row.
>> You want to get the timestamp literal either with the to_timestamp
>> function with the flexible formatting options or in the form the other
>>
> fellow suggested.
>
>> Unless you're trying to avoid using an index on the timestamp column
>> or feel a need to give your cpu a little extra exercise, then cast
>> your column all you want, but that is going to do extra work on each
>> row, which is a bad habit that can be significant.
>>
>> What release are you on that dbms_xplan.display_cursor is not working?
>> Perhaps you need to enable something. Good lord, I take that being
>> available as a given and I don't know how people live without it.
>>
>> mwf
>>
>> -----Original Message-----
>> From: oracle-l-bounce_at_freelists.org
>> [mailto:oracle-l-bounce_at_freelists.org]
>> On Behalf Of jo
>> Sent: Thursday, April 18, 2013 2:50 AM
>> Cc: oracle-l_at_freelists.org
>> Subject: Re: comparing datetime
>>
>> Hi Mark,
>>
>> I resolved the question by using CAST...
>>
>> SELECT ts_ultima_modifica FROM movimento_canina WHERE
>> CASTcast(ts_ultima_modifica AS TIMESTAMP)='2013-04-11
>> 10:14:52.782680';
>>
>> thanks for the help anyway.
>>
>> ps:
>>
>> by the way, the query
>> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS
>> LAST')); doesn't work.
>>
>>
>>
>> Mark W. Farnham wrote:
>>
>>
>>> If you want an explicit conversion of a constant to a timestamp, use
>>> to_timestamp:
>>>
>>> to_timestamp('2012-03-13 14:12:14.476865','YYYY-MM-DD
>>> HH24:MI:SS.FFTZD')
>>>
>>> If you want to see what your query is using for an implicit timestamp
>>> conversion, then run your query and then run
>>>
>>> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS
>>> LAST'));
>>>
>>> and you should see the comparison actually being done in a filter
>>>
>>>
>> statement.
>>
>>
>>> Then, seeing what Oracle is doing might help you see what is wrong.
>>>
>>> -----Original Message-----
>>> From: oracle-l-bounce_at_freelists.org
>>> [mailto:oracle-l-bounce_at_freelists.org]
>>> On Behalf Of Jose Soares
>>> Sent: Wednesday, April 17, 2013 8:59 AM
>>> To: oracle-l_at_freelists.org
>>> Subject: comparing datetime
>>>
>>> Hi all,
>>>
>>> I'm having some troubles comparing datetimes in oracle... what's
>>> wrong with these queries:
>>>
>>>
>>> file: login.sql
>>>
>>> alter session set nls_timestamp_tz_format='YYYY-MM-DD
>>> HH24:MI:SS.FFTZD';
>>>
>>> select codice_bdn,data_invio_entrata from movimento_canina join
>>> tipo_movimento_canina on
>>> tipo_movimento_canina.codice=cod_tipo_movimento_entrata and
>>> data_invio_entrata>'2012-03-13 14:12:14.476865' and rownum = 1;
>>>
>>> select codice_bdn,data_invio_entrata from movimento_canina join
>>> tipo_movimento_canina on
>>> tipo_movimento_canina.codice=cod_tipo_movimento_entrata and
>>> data_invio_entrata='2012-03-13 14:12:14.476865' and rownum = 1;
>>>
>>> select codice_bdn,data_invio_entrata from movimento_canina join
>>> tipo_movimento_canina on
>>> tipo_movimento_canina.codice=cod_tipo_movimento_entrata and
>>> data_invio_entrata<'2012-03-13 14:12:14.476865' and rownum = 1;
>>>
>>>
>>>
>>> $ sqlplus uri
>>>
>>> Session altered.
>>>
>>> CODI DATA_INVIO_ENTRATA
>>> ----
>>>
>>>
>>>
>> ----------------------------------------------------------------------
>> -----
>>
>>
>>> 0 2012-03-13 14:12:14.476865
>>>
>>> no rows selected
>>>
>>> no rows selected
>>>
>>> SQL>
>>>
>>> Why data_invio_entrata > '2012-03-13 14:12:14.476865' if they
>>> contains the same value?
>>> j
>>>
>>> --
>>> http://www.freelists.org/webpage/oracle-l
>>>
>>>
>>> --
>>> http://www.freelists.org/webpage/oracle-l
>>>
>>>
>>>
>>>
>>>
>>
>>
>
>
>
--
Jose Soares Da Silva _/_/
Sferacarta Net
Via Bazzanese 69 _/_/ _/_/_/
40033 Casalecchio di Reno _/_/ _/_/ _/_/
Bologna - Italy _/_/ _/_/ _/_/
Ph +39051591054 _/_/ _/_/ _/_/ _/_/
fax +390516131537 _/_/ _/_/ _/_/ _/_/
web:www.sferacarta.com _/_/_/ _/_/_/
Le informazioni contenute nella presente mail ed in ogni eventuale file allegato sono riservate e, comunque, destinate esclusivamente alla persona o ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, l’integrità e la sicurezza della presente mail non possono essere garantite. Se avete ricevuto questa mail per errore, Vi preghiamo di contattarci immediatamente e di eliminarla. Grazie.
This communication is intended only for use by the addressee, pursuant to legislative decree 30 June 2003, n. 196. It may contain confidential or privileged information. You should not copy or use it to disclose its contents to any other person. Transmission cannot be guaranteed to be error-free, complete and secure. If you are not the intended recipient and receive this communication unintentionally, please inform us immediately and then delete this message from your system. Thank you.
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Apr 19 2013 - 09:11:17 CEST