Re: equivalent DataDiff function?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 24 Jun 1999 15:31:49 GMT
Message-ID: <37764d2a.66700410@newshost.us.oracle.com>

A copy of this was sent to wykoff_at_my-deja.com (if that email address didn't require changing) On Thu, 24 Jun 1999 15:13:40 GMT, you wrote:

>Hi,
>
>I'm converting a report generating application from using SQL Server to
>Oracle. The SQL contains a call to the DateDiff function and selecting
>only rows where the difference is under a specified time.
>
>It seems that Oracle 7.3.4 is really lean on the date (and string)
>functions it provides. I looked at MONTHS_BETWEEN, but for my
>application it will allow too wide of a range of dates. This is the
>only function I saw that calculates a difference.
>
>If you know of more date functions, please let me know.
>

SQL> set serveroutput on
SQL> 
SQL> declare
  2          a       date;
  3          b       date;
  4  begin
  5          a := sysdate;
  6          dbms_lock.sleep(10);    -- sleep about 10 seconds give or take
  7          b := sysdate;
  8  
  9          dbms_output.put_line( b-a || ' of a day has elapsed' );
 10          dbms_output.put_line( (b-a)*24 || ' of an hour has elapsed' );
 11          dbms_output.put_line( (b-a)*24*60 || ' of a minute has elapsed' );
 12          dbms_output.put_line( (b-a)*24*60*60 || ' seconds has elapsed' );

 13 end;
 14 /

.000127314814814814814814814814814814814815 of a day has elapsed
.00305555555555555555555555555555555555556 of an hour has elapsed
.1833333333333333333333333333333333333336 of a minute has elapsed
11.00000000000000000000000000000000000002 seconds has elapsed

PL/SQL procedure successfully completed.

date arithmetic is so trivial that a specialized function like datediff is not needed. Just subtract. You get the difference in days. Multiply by 24 -- hours, multiply by 60 minutes, multiply by 60 -- seconds.

If you want to do it in SQL:

select to_char( created, 'dd-mon-yyyy hh24:mi:ss' ),

       trunc( sysdate-created ) "Dy",
       trunc( mod( (sysdate-created)*24, 24 ) )  "Hr",
       trunc( mod( (sysdate-created)*24*60, 60 ) )  "Mi",
 trunc( mod( to_char(sysdate,'SSSSS')-to_char(created,'SSSSS'), 60 ) ) "Sec",
       to_char( sysdate, 'dd-mon-yyyy hh24:mi:ss' ),
       sysdate-created "Tdy",
       (sysdate-created)*24 "Thr",
       (sysdate-created)*26*60 "Tmi",
       (sysdate-created)*26*60*60 "Tsec"

from all_users
where rownum < 50
/

Dy gives you number of days between 2 dates (partial days discarded). Tdy gives you total days including fractions (eg: you'll get 1.5 for 1 and 1/2 days)

Hr/Thr = hours
Mi/Tmi = minutes
Sec/Tsec = 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

>In the meantime I'm trying to write my own function. I was hoping that
>by assigning the date to a long, the date would be 'implicitly'
>transformed to a long, then I could do arithmetic on the dates to get
>the difference. Unfortunately what seemed to happen is that my longs
>got transformed into dates and I could no longer do arithmetic on them
>although they did print out nicely in the default time format (argghh).
>
>Also, from the lack of documentation about arrays, I'd have to assume
>that PL/SQL doesn't allow for arrays aside from those meant for row
>retrieval.
>

they are not called arrays, they are called collection types and plsql table types. Here is an example:

SQL> declare

  2      type myArray is table of number index by binary_integer;
  3      x   myArray;
  4  begin
  5      for i in 1 .. 10 loop
  6         x(i) := i;
  7      end loop;
  8      for i in 1 .. x.count loop
  9        dbms_output.put_line( x(i) );
 10      end loop;

 11 end;
 12 /
1
2
3
4
5
6
7
8
9
10

PL/SQL procedure successfully completed.

>Does anyone think that PL/SQL is good? :)
>

I myself think it is awesome (especially compared to say TSQL :)

Its got alot of attributes of 3gls, packages are really great (session persistent variables, hidden functions, the ability to write really big modular programs, etc), dynamic sql, cursor variables, etc etc etc....

Once you learn it, you'll wonder how you ever managed with just tsql to code with.

>Thanks,
>kim
>
>ps: sorry if this got posted twice, but my usual new server seems to not
>be working well.
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
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 Thu Jun 24 1999 - 10:31:49 CDT