CODEWITHSUNDEEP
oracle-databaseoracle11g
Dedi Setiawan
Dedi Setiawan

Reputation: 25

Calculate total time in oracle

i have same problem like here

how to calculate sum time with data type char in oracle

but little different. i have 2 table like this :

table employee

emp_id   emp_name   emp_birth_date

123456   sacha      18/07/1980

Using this query

create table employee (emp_id char(10), emp_name char(10), emp_birth_date date);
insert into employee values ('123456', 'sacha', (TO_DATE('18/07/1980', 'dd/mm/yyyy')));

and table dept

table dept

emp_id    emp_reg_date   emp_time_in    emp_time_off

123456    25/12/2011     10:00:00       19:00:00

using this query

create table dept (emp_id char(10), emp_reg_date date, emp_time_in char(10), emp_time_off char(10));
insert into dept values ('123456', (TO_DATE('25/12/2011', 'dd/mm/yyyy')), '10:00:00', '19:00');

all data type is char except birth_date and reg_date

i can display emp_id, emp_name, emp_reg_date, emp_time_in, emp_time_off using this query select employee.emp_id, employee.emp_name, dept.emp_date_reg, dept.emp_time_in, dept.emp_time_off from employee, dept where employee.emp_id = dept.emp_id;

but how to calculate total time in table dept for emp_time_in and emp_time_off for a day and a month?

Upvotes: 0

Views: 1320

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

What an awful design; what made you create EMP_TIME_IN and EMP_TIME_OFF VARCHAR2 columns? Those should have been DATE ones. I suggest you change that.

Meanwhile, you'll have to concatenate EMP_REG_DATE and those IN and OFF columns in order to get DATE value; then, by subtracting two dates, you'd get number of DAYS and - using a little bit of mathematics - get hours, minutes, or whatever you want.

For example:

SQL> create table test
  2    (empno        number,
  3     emp_reg_date date,
  4     emp_time_in  varchar2(10),
  5     emp_time_off varchar2(10));

Table created.

SQL> insert into test values (1, date '2018-03-20', '10:00', '19:00');

1 row created.

SQL> insert into test values (1, date '2018-03-21', '11:30', '12:30');

1 row created.

SQL> insert into test values (2, date '2018-03-25', '13:00', '16:20');

1 row created.

Employee 1 worked 9 hours + 1 hour = 10 hours in total. Employee 2 worked 3 hours 20 minutes.

SQL> with dates as
  2    (select
  3       empno,
  4       to_date(to_char(emp_reg_date, 'dd.mm.yyyy') || emp_time_in , 'dd.mm.yyyy hh24:mi') date_in,
  5       to_date(to_char(emp_reg_date, 'dd.mm.yyyy') || emp_time_off, 'dd.mm.yyyy hh24:mi') date_off
  6     from test
  7    ),
  8  summary as
  9    (select empno,
 10       sum(date_off - date_in) diff_days
 11     from dates
 12     group by empno
 13    )
 14  select empno,
 15    trunc(diff_days * 24) hours,
 16    round((diff_days * 24 - trunc(diff_days * 24)) * 60) minutes
 17  from summary;

     EMPNO      HOURS    MINUTES
---------- ---------- ----------
         1         10          0
         2          3         20

SQL>

Note that there's practically no control over what you enter into VARCHAR2 columns TIME_IN and TIME_OUT; what prevents you from entering AX:FM or 99:45 or A-b-e_XF in there? All those are valid strings, but invalid times.

Once again: fix data model.

Upvotes: 1

Related Questions