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

joel garry (joel-garry_at_home.com) wrote:
: On Oct 9, 3:14=A0am, Laurenz Albe <inv..._at_spam.to.invalid> wrote:
: > m..._at_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,28): 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 Received on Fri Oct 10 2008 - 21:18:20 CDT