Solved

How to caluate the date Difference in Oracle

Posted on 2003-11-10
7
2,334 Views
Last Modified: 2010-05-18
Dear Expert,

      I have a table  employee_attendance which keeps track the time_in,time_out (they are date format in database) for an employee every day.There is a third attribute  called workhours(calculated attribute actually),  which calculates  the workhours between these two time_in, time_out, and round the difference to quarter, for example, if the employee works 18minutes(0.3hrs), that should be 0.5hrs(two quarters).

The presentation of the result should be like this:

time_in                   time_out                work_hours
11/07/2003  08:00   11/07/2003 09:13     1.25
11/10/2003  10:55   11/10/2003 11:13      0.5
.........

Please tell me how to write the formula for the caluated attribute -  workhours?

Thanks,

Huer
0
Comment
Question by:huer
7 Comments
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 32 total points
ID: 9718236
Which version of Oracle do you have?  Oracle9i includes many more built-ins for dates and times than earlier versions of Oracle.

I wrote a PL\SQL function some years ago that works with Oracle7 and 8 to calculate the time between two dates.  I'll post that here, but it does not round to the nearest 1/4 hour.  You would need to modify it to get that effect.  I think if you divide the hours by 60 (to get the number of minutes), then add 7.5 minutes(to get the effect of rounding up to the nearest quarter hour), then divide by 15, rounded to 0 (to get the number of quarter hours) then divide that by four, rounded to two digits, you should get the number of hours to the nearest quarter hour.

create or replace function time_between (start_tm in date, end_tm in date,
   hours_only varchar2 default 'N') return varchar2 as
-- If "hours_only" is null or "N", the return will be a string formatted like:
--     2 days, 3 hrs, 5 mins, 10 secs
-- If "hours_only" is not "N", then the return is a value in hours, like 102.325
  ret_val   varchar2(80);
  start_sec number;
  end_sec   number;
  full_sec  number;
  balance   number;
  minutes   number;
  hours     number;
  days      number;
--
  function get_sec (time_in in date) return number as
  begin
    return to_number(to_char(time_in,'SSSSS'));
  end;
--
begin
  start_sec := get_sec(start_tm);
  end_sec := get_sec(end_tm);
  -- check if end time is in the same day as start time
  if to_char(start_tm,'YYMMDD') = to_char(end_tm,'YYMMDD') then
    full_sec := end_sec - start_sec;
    days := 0;
  else
    days := trunc(end_tm - start_tm);
    if days > 0 then
      ret_val := to_char(days)||' days, ';
    end if;
    if end_sec > start_sec then
      full_sec := end_sec - start_sec;
    else
      full_sec := 86400 - start_sec + end_sec;
    end if;
  end if;
  if upper(hours_only) = 'N' then
    if full_sec > 3599 then
      hours := floor(full_sec / 3600);
      balance := mod(full_sec,3600);
      full_sec := balance;
      if hours > 1 then
        ret_val := ret_val || to_char(hours) ||' hrs, ';
      else
        ret_val := ret_val || to_char(hours) ||' hr, ';
      end if;    
    end if;
    if full_sec > 59 then
      minutes := floor(full_sec / 60);
      balance := mod(full_sec,60);
      full_sec := balance;
      if minutes > 1 then
        ret_val := ret_val||to_char(minutes)||' mins, ';
      else
        ret_val := ret_val||to_char(minutes)||' min, ';
      end if;    
    end if;
    ret_val := ret_val||to_char(full_sec)||' secs';
  else
    -- Calculate the time difference in hours, to three decimal places
    ret_val := to_char((24 * days) + round((full_sec / 3600),3));
  end if;
  return ret_val;
end;
/
grant execute on time_between to public;
create public synonym time_between for time_between;
0
 
LVL 8

Assisted Solution

by:Danielzt
Danielzt earned 31 total points
ID: 9719274
try this:


create table test3(
time_in date, time_out date);

insert into test3
values(to_date('11/07/2003  08:00','mm/dd/yyyy hh24:mi'),
to_date('11/07/2003 09:13','mm/dd/yyyy hh24:mi'));
insert into test3
values(to_date('11/10/2003  10:55','mm/dd/yyyy hh24:mi'),
to_date('11/10/2003 11:13','mm/dd/yyyy hh24:mi'));

insert into test3
values(to_date('11/10/2003  10:55','mm/dd/yyyy hh24:mi'),
to_date('11/11/2003 11:13','mm/dd/yyyy hh24:mi'));
         
insert into test3
values(to_date('11/10/2003  08:00','mm/dd/yyyy hh24:mi'),
to_date('11/10/2003 16:30','mm/dd/yyyy hh24:mi'));

----------------
select
floor(time_out - time_in)*24 + floor((time_out - time_in-floor(time_out - time_in) )*24)+
decode( floor( ( ((time_out - time_in)*24 - FLOOR((time_out - time_in)*24))*60+14 )/15 ),
 0,0,1,0.25,2,0.5,3,0.75)
FROM TEST3

0
 
LVL 8

Assisted Solution

by:gajender_99
gajender_99 earned 31 total points
ID: 9721040
hi try this simple formula

select  (dur*60+(15 - round(mod((dur*60),15))))/60
from (
select (time_out -time_in) *24 dur
 from employee_attendance )

and then you can use the out_put to replace the work_hours

0
 
LVL 3

Assisted Solution

by:taisk
taisk earned 31 total points
ID: 9726432
What about this?  It seems to satisfy your requirement.  I have set it up as an update statement.

UPDATE employee_attendance
SET work_hours = CEIL(((time_out - time_in) * 24 * 60)/15)*15/60
WHERE <criteria goes here>
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 9731475
Hello huer.  Are you checking the responses to your question?  As you can see, there is no simple way to get what you asked for, but you have a few options here to choose from.  Have you tried them?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now