comparing a DATE column with "one minute ago", type warning?

On Oct 8, 7:12 pm, m....RemoveThis@pixar.com wrote:
> How should I be comparing a DATE column with "one minute ago"?
>
> I do this:
>     select sysdate, sysdate- interval '1' minute from dual;
>
> and get just what I need, the time a minute ago:
>
>     SYSDATE                   SYSDATE-INTERVAL'1'MINUTE
>     ------------------------- -------------------------
>     08-OCT-08 17:09:19        08-OCT-08 17:08:19        
>
> But in my PL/SQL, I get a warning on this code, where
> lastping is a DATE column:
>
>         update mytable
>            set status='silent'
>          where lastping < (sysdate - interval '1' minute);
>                            ^       ^
>                       col 28     col 36
>
> Warning(16,2: PLW-07202: bind type would result in conversion
>                 away from column type
> Warning(16,36): PLW-07202: bind type would result in conversion
>                 away from column type
>
> Many TIA!
> Mark
>
> --
> Mark Harrison
> Pixar Animation Studios

You could also do this:

update mytable
set status='silent'
where lastping < (sysdate - 1/1440);

and achieve the same result.



David Fitzjarrell
 >> Stay informed about: comparing a DATE column with "one minute ago", type warning? 

mh.RemoveThis@pixar.com wrote:
> How should I be comparing a DATE column with "one minute ago"?
>
> I do this:
> select sysdate, sysdate- interval '1' minute from dual;
>
> and get just what I need, the time a minute ago:
>
> SYSDATE SYSDATE-INTERVAL'1'MINUTE
> ------------------------- -------------------------
> 08-OCT-08 17:09:19 08-OCT-08 17:08:19
>
> But in my PL/SQL, I get a warning on this code, where
> lastping is a DATE column:
>
> update mytable
> set status='silent'
> where lastping < (sysdate - interval '1' minute);
> ^ ^
> col 28 col 36
>
> Warning(16,2: PLW-07202: bind type would result in conversion
> away from column type
> Warning(16,36): PLW-07202: bind type would result in conversion
> away from column type

I'd say that the warning is bogus and should be ignored.

According to
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_ele...ts001.h
the difference between DATE and INTERVAL is a DATE, so if lastping is
a DATE there should be no implicit conversion necessary.

Yours,
Laurenz Albe
 >> Stay informed about: comparing a DATE column with "one minute ago", type warning? 

On Oct 9, 3:14 am, Laurenz Albe <inv... DeleteThis @spam.to.invalid> wrote:
> m... DeleteThis @pixar.com wrote:
> > How should I be comparing a DATE column with "one minute ago"?
>
> > I do this:
> >    select sysdate, sysdate- interval '1' minute from dual;
>
> > and get just what I need, the time a minute ago:
>
> >    SYSDATE                   SYSDATE-INTERVAL'1'MINUTE
> >    ------------------------- -------------------------
> >    08-OCT-08 17:09:19        08-OCT-08 17:08:19        
>
> > But in my PL/SQL, I get a warning on this code, where
> > lastping is a DATE column:
>
> >        update mytable
> >           set status='silent'
> >         where lastping < (sysdate - interval '1' minute);
> >                           ^       ^
> >                      col 28     col 36
>
> > Warning(16,2: PLW-07202: bind type would result in conversion
> >                away from column type
> > Warning(16,36): PLW-07202: bind type would result in conversion
> >                away from column type
>
> I'd say that the warning is bogus and should be ignored.
>
> According tohttp://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_el...
> the difference between DATE and INTERVAL is a DATE, so if lastping is
> a DATE there should be no implicit conversion necessary.
>
> Yours,
> Laurenz Albe

I'd say you are incorrectly assuming that since the difference is a
DATE and one of the operands is a DATE, there is no conversion. But
if you scroll up a bit from where your link points, you see "When you
pass a timestamp, interval, or numeric value to a built-in function
that was designed only for the DATE datatype, Oracle implicitly
converts the non-DATE value to a DATE value..." I'd agree that it
pointing to sysdate as part of the problem is kind of strange, but
code rules over docs, especially when coding.

jg
--
@home.com is bogus. "The Fox News story is wrong and is riddled with
falsehoods and errors. The story cites misinformation from
unattributed sources and leaked emails that are taken out of
context..." http://www.foxnews.com/story/0,2933,435681,00.html
 >> Stay informed about: comparing a DATE column with "one minute ago", type warning? 

joel garry (joel-garry@home.com) wrote:
: On Oct 9, 3:14=A0am, Laurenz Albe <inv....DeleteThis@spam.to.invalid> wrote:
: > m....DeleteThis@pixar.com wrote:
: > > How should I be comparing a DATE column with "one minute ago"?
: >
: > > I do this:
: > > =A0 =A0select sysdate, sysdate- interval '1' minute from dual;
: >
: > > and get just what I need, the time a minute ago:
: >
: > > =A0 =A0SYSDATE =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 SYSDATE-INTERVAL'1'M=
: INUTE
: > > =A0 =A0------------------------- -------------------------
: > > =A0 =A008-OCT-08 17:09:19 =A0 =A0 =A0 =A008-OCT-08 17:08:19 =A0 =A0 =A0=
: =A0
: >
: > > But in my PL/SQL, I get a warning on this code, where
: > > lastping is a DATE column:
: >
: > > =A0 =A0 =A0 =A0update mytable
: > > =A0 =A0 =A0 =A0 =A0 set status=3D'silent'
: > > =A0 =A0 =A0 =A0 where lastping < (sysdate - interval '1' minute);
: > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ^ =A0 =A0 =A0 ^
: > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0col 28 =A0 =A0 col 36
: >
: > > Warning(16,2: PLW-07202: bind type would result in conversion
: > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0away from column type
: > > Warning(16,36): PLW-07202: bind type would result in conversion
: > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0away from column type
: >
: > I'd say that the warning is bogus and should be ignored.
: >
: > According tohttp://download.oracle.com/docs/cd/B19306_01/server.102/b1420=
: 0/sql_el...
: > the difference between DATE and INTERVAL is a DATE, so if lastping is
: > a DATE there should be no implicit conversion necessary.
: >
: > Yours,
: > Laurenz Albe

: I'd say you are incorrectly assuming that since the difference is a
: DATE and one of the operands is a DATE, there is no conversion. But
: if you scroll up a bit from where your link points, you see "When you
: pass a timestamp, interval, or numeric value to a built-in function
: that was designed only for the DATE datatype, Oracle implicitly
: converts the non-DATE value to a DATE value..." I'd agree that it
: pointing to sysdate as part of the problem is kind of strange, but
: code rules over docs, especially when coding.

I don't see him passing anything into a built in function. He used
subtraction as an operator of the PL/SQL language.

Surely if using a DATE and an INTERVAL involves a questionable implicit
conversion then using a DATE and a number (1/1440 from another post)
should be even more questionable.

One might ask - what is the purpose of having a specific interval data
type - surely it is to allow you make exactly this sort of calculation and
to avoid unexpected issues when working with dates.

I am reminded of VMS which provides two closely related date types -
absolute times and delta time, which are two uses of the same underlying,
well defined, operating system supported, data structure, and which is
quite explicitly different than (say) a numeric value (of which there are
also many well defined types supported by the operating system).


$0.10
 >> Stay informed about: comparing a DATE column with "one minute ago", type warning? 

joel garry <joel-garry DeleteThis @home.com> wrote:
>> > where lastping < (sysdate - interval '1' minute);
>> > ^ ^
>> > col 28 col 36
>>
>> > Warning(16,2: PLW-07202: bind type would result in conversion
>> > away from column type
>> > Warning(16,36): PLW-07202: bind type would result in conversion
>> > away from column type
>>
>> I'd say that the warning is bogus and should be ignored.
>>
>> According tohttp://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_el...
>> the difference between DATE and INTERVAL is a DATE, so if lastping is
>> a DATE there should be no implicit conversion necessary.
>
> I'd say you are incorrectly assuming that since the difference is a
> DATE and one of the operands is a DATE, there is no conversion. But
> if you scroll up a bit from where your link points, you see "When you
> pass a timestamp, interval, or numeric value to a built-in function
> that was designed only for the DATE datatype, Oracle implicitly
> converts the non-DATE value to a DATE value..."

Hmm, it would be nice if you could explain that in more detail, because
to me it still apears like this:

- The operator "-" is not "designed only for the DATE datatype",
so the quotation should not apply in this case, right?
- There should be no conversion necessary because, in this case, the
function "-" is used on a DATE and an INTERVAL DAY TO SECOND argument,
and the DATE that (according to the documentation) is supposed to
result is compared to a DATE column.

Where do I go wrong?

> I'd agree that it
> pointing to sysdate as part of the problem is kind of strange, but
> code rules over docs, especially when coding.

Those are wise words.

Two things feed my doubt here:
- I have frequently encountered wrong warnings from the PL/SQL
compiler, for example about "unreachable code" that was happily reached
later.
- As has been remarked, there is no warning for "sysdate - 1/1440", yet
according to the documentation:
"If one operand is a DATE value or a numeric value ...
... Oracle implicitly converts the other operand to DATE data."
So there should be a warning in this case, no?

Yours,
Laurenz Albe
 >> Stay informed about: comparing a DATE column with "one minute ago", type warning? 

mh.RemoveThis@pixar.com said:
>How should I be comparing a DATE column with "one minute ago"?
....
>But in my PL/SQL, I get a warning on this code, where
>lastping is a DATE column:
>
> update mytable
> set status='silent'
> where lastping < (sysdate - interval '1' minute);
> ^ ^
> col 28 col 36
>
>Warning(16,2: PLW-07202: bind type would result in conversion
> away from column type
>Warning(16,36): PLW-07202: bind type would result in conversion
> away from column type

I encountered this same today, and found out that this is a known
Oracle bug; in Oracle MetaLink bug database this is identified by bug
number 5895688. Based on Oracle documentation (thanks for everyone
in this thread for providing the proper starting points in the docs!),
I would claim that your syntax is correct. The bug was reported to exist
on 10.2.0.1, I did encounter it on 10.2.0.3 (Linux x86-64), and based
on the comments on MetaLink, it was not yet resolved.

I also did find a workaround for this. You can avoid the warning
by creating a variable of the correct type to hold the intermediate
result, and the use that variable instead of the expression in
your SQL statement. So, instead of

begin
update mytable
set status='silent'
where lastping < (sysdate - interval '1' minute);
end;

use the below form:

declare
time_limit date;
begin
time_limit := sysdate - interval '1' minute;
update mytable
set status='silent'
where lastping < time_limit;
end;

If you end up using the above workaround, it might be good to place
a comment into your code telling that the extra variable is used
solely to circumvent Oracle bug 5895688 (as a reminder for
a future maintainer that the code may be cleaned up, if the Oracle
bug has been fixed).
--
Wolf a.k.a. Juha Laiho Espoo, Finland
(GC 3.0) GIT d- s+: a C++ ULSH++++$ P++@ L+++ E- W+$@ N++ !K w !O !M V
PS(+) PE Y+ PGP(+) t- 5 !X R !tv b+ !DI D G e+ h---- r+++ y++++
"...cancel my subscription to the resurrection!" (Jim Morrison)
 >> Stay informed about: comparing a DATE column with "one minute ago", type warning? 

Juha Laiho <Juha.Laiho.RemoveThis@iki.fi> wrote:
> I also did find a workaround for this. You can avoid the warning
> [...]

That works perfectly... thanks!

Mark

--
Mark Harrison
Pixar Animation Studios
 >> Stay informed about: comparing a DATE column with "one minute ago", type warning? 

On Oct 29, 7:38 pm, m....DeleteThis@pixar.com wrote:
> Juha Laiho <Juha.La....DeleteThis@iki.fi> wrote:
> > I also did find a workaround for this. You can avoid the warning
> > [...]
>
> That works perfectly... thanks!
>
> Mark
>

Thanks from me too, and kudos to Laurenz for a good call.

jg
--
@home.com is bogus. French novelist Honoré de Balzac (1799-1850) died
of caffeine poisoning, the likely consequence of his reported
consumption of more than 50 cups of coffee per day.
 >> Stay informed about: comparing a DATE column with "one minute ago", type warning?