Re: Subtract Hours From A Given Date

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.

but since we only need 5 digits anyway.... it doesn't matter.

Since a second (the smallest portion of a day we hold) only needs about 5 digits of precision (make it six to be safe) using an Oracle Number with 38 digits is pretty safe -- no need to worry about that pesky floating point number with 6 or 13 digits of precision you have in native floats.

--
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 - 14:05:37 CDT