equivalent DataDiff function?

equivalent DataDiff function?

Post by wyk.. » Fri, 25 Jun 1999 04:00:00



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.

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.

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

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.

 
 
 

equivalent DataDiff function?

Post by Thomas Ky » Fri, 25 Jun 1999 04:00:00



(if that email address didn't require changing)


>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

Quote:>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.

Quote:>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.

Quote:>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


Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation

 
 
 

equivalent DataDiff function?

Post by wyk.. » Fri, 25 Jun 1999 04:00:00




> 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.

Wow, that was awesome.  Thanks so much.  Why, oh why can't I find this
documented?

kim

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

 
 
 

equivalent DataDiff function?

Post by Thomas Ky » Fri, 25 Jun 1999 04:00:00



(if that email address didn't require changing)




>> 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.

>Wow, that was awesome.  Thanks so much.  Why, oh why can't I find this
>documented?

>kim

>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

from the oracle7 "sql reference" manual. (same place in the o8 docs as well)
chapter 2 - elements of oracle7 sql
section on datatypes/Date datatype:

<quote>
DATE Datatype

The DATE datatype is used to store date and time information.
Although date and time information can be represented in both CHAR
and NUMBER datatypes, the DATE datatype has special associated
properties.
For each DATE value the following information is stored:
? century
? year
? month
? day
? hour
? minute
? second

To specify a date value, you must convert a character or numeric value
to a data value with the TO_DATE function. Oracle7 automatically
converts character values that are in the default date format into date
values when they are used in date expressions. The default date format
is specified by the initialization parameter NLS_DATE_FORMAT and is
a string such as DDMONYY. This example date format includes a
twodigit number for the day of the month, an abbreviation of the
month name, and the last two digits of the year.

If you specify a date value without a time component, the default time
is 12:00:00a.m. (midnight). If you specify a date value without a date,
the default date is the first day of the current month.
The date function SYSDATE returns the current date and time. For
information on the SYSDATE and TO_DATE functions and the default
date format, see Chapter 3 Operators, Functions, Expressions,
Conditions of this manual.

Date Arithmetic
===============
You can add and subtract number constants as well as other dates from
dates. Oracle7 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.

Oracle7 provides functions for many of the common date operations.
For example, the ADD_MONTHS function allows you to add or
subtract months from a date. The MONTHS_BETWEEN function
returns the number of months between two dates. The fractional
portion of the result represents that portion of a 31day month. For
more information on date functions, see the section Date Functions
on page 3 37.

Because each date contains a time component, most results of date
operations include a fraction. This fraction means a portion of one day.
For example, 1.5 days is 36 hours.

Using Julian Dates
==================
A Julian date is the number of days since Jan 1, 4712 BC. Julian dates
allow continuous dating from a common reference. You can use the
date format model J with date functions TO_DATE and TO_CHAR
to convert between Oracle7 DATE values and their Julian equivalents.
This statement returns the Julian equivalent of January 1, 1992:
SELECT TO_CHAR(TO_DATE(01011992, MMDDYYYY),J)
FROM DUAL
TO_CHAR(TO_DATE(01011992,MMDDYYYY),J)

2448623

</quote>

--
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


Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation