Path: text.usenetserver.com!out04b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!cycny01.gnilink.net!cyclone1.gnilink.net!gnilink.net!nx01.iad.newshosting.com!newshosting.com!216.196.98.140.MISMATCH!Xl.tags.giganews.com!border1.nntp.dca.giganews.com!nntp.giganews.com!local02.nntp.dca.giganews.com!nntp.posted.cspinternet!news.posted.cspinternet.POSTED!not-for-mail NNTP-Posting-Date: Fri, 10 Oct 2008 20:18:23 -0500 From: yf110@vtn1.victoria.tc.ca (Malcolm Dew-Jones) Subject: Re: comparing a DATE column with "one minute ago", type warning? Newsgroups: comp.databases.oracle.misc References: <1223547245.636520@proxy.dienste.wien.at> <4d91256d-9145-43dc-97db-5687864eb164@f37g2000pri.googlegroups.com> Organization: Victoria Telecommunity Network X-Newsreader: TIN [version 1.2 PL2] X-Original-NNTP-Posting-Host: 199.60.222.3 Message-ID: <48effedc$1@news.victoria.tc.ca> Date: 10 Oct 2008 18:18:20 -0800 X-Original-Trace: 10 Oct 2008 18:18:20 -0800, 199.60.222.3 XPident: yf110 Lines: 69 X-Usenet-Provider: http://www.giganews.com NNTP-Posting-Host: 199.60.222.1 X-Trace: sv3-gBDbezDdIiQSbCWJLow7LW8fPFtz8CTSZfmfOOScA0ZZeC1b8tMHx3c+3FQEyoHa4QS2ZGMOv+c2nWF!geoa+yIRpl1TotMj0LJaelh2PCBWSm+WoI5RNw94+v6590zyEAx4B0j2gwhEiwf8t2Ly X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly X-Postfilter: 1.3.39 Bytes: 4302 Xref: usenetserver.com comp.databases.oracle.misc:254021 X-Received-Date: Fri, 10 Oct 2008 21:18:24 EDT (text.usenetserver.com) joel garry (joel-garry@home.com) wrote: : On Oct 9, 3:14=A0am, Laurenz Albe wrote: : > m...@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