Re: comparing datetime
- From: jo <jose.soares@xxxxxxxxxxxxxx>
- To: mwf@xxxxxxxx
- Date: Thu, 18 Apr 2013 11:22:45 +0200
Hi Mark,
I choose to use CAST instead of to_timestamp because I'm using
sqlalchemy to access even other databases using the same code
like postgres for example and to_timestamp doesn't work on postgres
thus I must to remain compatible.
the query that you sent me give me this error:
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS
LAST'));
select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'))
*
ERROR at line 1:
ORA-00907: missing right parenthesis
this is the version:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
anyway I don't have privileges as administrator to that db.
------------------------
I don't know if it depends from the db version or any other thing. :-\
because I tried the SELECT ts_ultima_modifica FROM movimento_canina
WHERE ts_ultima_modifica='2013-04-11 10:14:52.782680'; (without CAST)
on another oracle database
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
and it works correctly but
select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));
plan_table_output
-----------------------------------------
User has no SELECT privilege on V$SESSION
(1 rows)
j
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of jo
> Sent: Thursday, April 18, 2013 2:50 AM
> Cc: oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxxxxxx
>> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
>> On Behalf Of Jose Soares
>> Sent: Wednesday, April 17, 2013 8:59 AM
>> To: oracle-l@xxxxxxxxxxxxx
>> 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
>>
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>> --
>> //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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
