| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Subtract Hours From A Given Date
Thomas Kyte wrote:
>
> A copy of this was sent to Thomas Kyte <tkyte_at_us.oracle.com>
> (if that email address didn't require changing)
> On Fri, 10 Sep 1999 15:05:37 -0400, you wrote:
>
> >A copy of this was sent to Kenneth C Stahl <BlueSax_at_Unforgettable.com>
> >(if that email address didn't require changing)
> >On Fri, 10 Sep 1999 13:33:10 -0400, you wrote:
> >
> >>Thomas Kyte wrote:
> >>
> >>> On Fri, 10 Sep 1999 10:02:33 -0400, you wrote:
> >>>
> >>> >Thomas Kyte wrote:
> >>> >
> >>> >> or
> >>> >>
> >>> >> select (sysdate - 5/24) from dual
> >>> >>
> >>> >
> >>> >I don't trust that method since it depends on irrational numbers.
> >>>
> >>> trust it or not -- it works and is counted on working (its the way to do it)...
> >>>
> >>
> >>I'm not convinced. I've never seen it done that way in Oracle literature - although
> >>I have seen it done by subtracting the product of 60 * 60 * n where n is the number
> >>of hours.
> >>
> >>Would you say that your method is the "official" way that Oracle recommends that
> >>hours be subtracted? Are you absolutely positive that there is absolutely no way
> >>that a problem can occur? I know, beyond any shadow of a doubt that the 60*60*n will
> >>work in every circumstance because it is guaranteed to always provide the correct
> >>answer.
> >>
> >
> >
> >From the Oracle7.x - Oracle8i SQL Language Manual (Oracle literature):
> >
> ><quote>
> >Date Arithmetic
> >
> >You can add and subtract number constants as well as other dates from dates.
> >Oracle interprets number constants in arithmetic date expressions as numbers of
> >days. For example, SYSDATE + 1 is tomorrow. SYSDATE - 7 is one week ago.
> >SYSDATE + (10/1440) is ten minutes from now. Subtracting the HIREDATE
> >column of the EMP table from SYSDATE returns the number of days since each
> >employee was hired. You cannot multiply or divide DATE values.
> ></quote>
> >
> >
> >sysdate + (10/1440)...
> >
> >10/1440 is a rational number....
> >0.006944444444444........ (lots of 4's) is not.
> >
>
> Wait a minute. I had to run home and get my old high school text book out :)
>
> 10/1440 is rational
> AND SO IS 0.006944444444444........ (lots of 4's)
>
> (and so is 5/24) -- in fact every number that can be represented as X/Y is
> rational. 10/1440 is a repeating number but it is not irrational.
>
> Not that is matters.....
>
> [snip]
>
> --
> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
Agreed...
Its only when you try add (say) the square root of 2 to a date that you start getting irrational numbers...
I'm still struggling to find a purpose for this though... :-) --
"Some days you're the pigeon, and some days you're the statue." Received on Fri Sep 10 1999 - 20:47:57 CDT
 |
 |