Re: Help needed with Date Functions

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 19 Apr 1999 13:52:26 GMT
Message-ID: <371c3245.855750@192.86.155.100>

A copy of this was sent to "Paul Wiles" <john_at_adzi.com> (if that email address didn't require changing) On Mon, 19 Apr 1999 14:31:06 +0100, you wrote:

>That's not what I'm after. I'm looking for a simple way to calculate the
>difference between two dates in seconds. The two dates may be months apart
>and I don't want to do any fancy arithmetic like working out the difference
>in days, multiplying by 24*3600 then adding and subtracting the seconds
>since midnight ('SSSS') for each of the boundary dates.
>
>Surely there must be a simpler way??

to get the time in seconds between 2 dates d1 and d2 you need to:

select (d2-d1)*24*60*60 from T

where did 'SSSSS' come into play? When you subtract 2 dates, you get the difference in floating point number in days. multiply by 24 to get hours. multiply by 60 to get minutes. mulitply by 60 again to get seconds. The difference between 2 dates is a fractional number inclusive of the seconds.

If you really want 'datediff' in your database, you can just do something like this:

SQL> create or replace function datediff( p_what in varchar2,
  2                                       p_d1   in date,
  3                                       p_d2   in date ) return number
  4  as
  5      l_result    number;
  6  begin
  7      select (p_d2-p_d1) *
  8             decode( upper(p_what),
  9                     'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL )
 10       into l_result from dual;
 11  
 11      return l_result;

 12 end;
 13 /

Function created.

Now, i just create a view to demonstrate with:

SQL> create or replace view temp_view
  2 as
  3 select to_date('01-JAN-1999 12:02:04', 'dd-mon-yyyy hh24:mi:ss' ) d1,   4 to_date('15-MAR-1999 01:34:23', 'dd-mon-yyyy hh24:mi:ss' ) d2   5 from dual
  6 /
View created.

SQL>
SQL> select datediff( 'ss', d1, d2 ) seconds from temp_view;

   SECONDS

---

   6269539

SQL> select datediff( 'mi', d1, d2 ) minutes from temp_view;

   MINUTES

---

104492.317

SQL> select datediff( 'hh', d1, d2 ) hours from temp_view;

     HOURS

---

1741.53861

>
>
>D. Buecherl wrote in message <371B223E.4CD736D7_at_nkk.net>...
>>date1 - date2 gives you the number of days between two dates, therefore
>>24 * 3600 * (sysdate - trunc(sysdate, 'MONTH'))
>>gives you the number of seconds elapsed within the current month
>>
>>Dieter
>>
>>Paul Wiles schrieb:
>>
>>> Hi
>>>
>>> Can someone tell me how to find the difference in seconds between two
>>> dates - is there an equivalent to the Transact SQL function:
>>>
>>> datediff(ss,date1,date2).
>>>
>>
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Apr 19 1999 - 08:52:26 CDT