Re: Subtract Hours From A Given Date

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 Received on Fri Sep 10 1999 - 16:29:53 CDT