Differences between two dates in Oracle which were in same column

Question

I have a table MyTable with columns:

ID   number       (autoincrement)
STAT number(3)    (status: 1-start, 2-stop)
USER varchar2(15) (user name)
DATE date         (date)

with data:

ID  STAT    USER    DATE
---------------------------------------
1   1   USER1   18.08.2010 13:10:14
2   2   USER1   18.08.2010 15:15:40
3   1   USER1   18.08.2010 16:15:10
4   2   USER1   18.08.2010 18:35:32
5   1   USER2   18.08.2010 07:05:46
6   2   USER2   18.08.2010 13:10:01
7   1   USER1   19.08.2010 09:01:10
8   2   USER1   19.08.2010 16:15:19
9   1   USER2   19.08.2010 11:02:56
10  2   USER2   19.08.2010 18:45:22

How do I get something like this:

USER        DATE       DURATION
--------------------------------
USER1   18.8.2010   04:25:48
USER2   18.8.2010   06:04:15
USER1   19.8.2010   07:14:09
USER2   19.8.2010   07:42:26

Answer 1

SELECT 
    USER,
    TO_CHAR(DATE, 'DD.MM.YYYY') DATE,
    TO_CHAR(DATE, 'HH24:MI:SS') DURATION
FROM MyTable 

Try this.

Answer 2

create table aa (id number(5), stat number(2), username varchar2(20), d date);

insert into aa
select 1,   1,   'USER1',   to_date('18.08.2010 13:10:14', 'DD.MM.YYYY HH24:MI:SS') from dual union all
select 2,   2,   'USER1',   to_date('18.08.2010 15:15:40', 'DD.MM.YYYY HH24:MI:SS') from dual union all
select 3,   1,   'USER1',   to_date('18.08.2010 16:15:10', 'DD.MM.YYYY HH24:MI:SS') from dual union all
select 4,   2,   'USER1',   to_date('18.08.2010 18:35:32', 'DD.MM.YYYY HH24:MI:SS') from dual union all
select 5,   1,   'USER2',   to_date('18.08.2010 07:05:46', 'DD.MM.YYYY HH24:MI:SS') from dual union all
select 6,   2,   'USER2',   to_date('18.08.2010 13:10:01', 'DD.MM.YYYY HH24:MI:SS') from dual union all
select 7,   1,   'USER1',   to_date('19.08.2010 09:01:10', 'DD.MM.YYYY HH24:MI:SS') from dual union all
select 8,   2,   'USER1',   to_date('19.08.2010 16:15:19', 'DD.MM.YYYY HH24:MI:SS') from dual union all
select 9,   1,   'USER2',   to_date('19.08.2010 11:02:56', 'DD.MM.YYYY HH24:MI:SS') from dual union all
select 10,  2,   'USER2',   to_date('19.08.2010 18:45:22', 'DD.MM.YYYY HH24:MI:SS') from dual 




    SELECT username, SomeDATE,
           to_char(to_date('00:00:00', 'HH24:MI:SS') + (duration), 'HH24:MI:SS') TIME
    FROM   (SELECT username, trunc(sd) AS SomeDate, SUM(duration) duration
             FROM   (SELECT id, Stat, username, d sd,
                             LEAD(d, 1) over(PARTITION BY username ORDER BY d, stat) - d duration
                      FROM   aa) t
             WHERE  Stat = 1
                    AND duration IS NOT NULL
             GROUP  BY username, trunc(sd))

output

USER1   19.08.2010  07:14:09
USER2   18.08.2010  06:04:15
USER2   19.08.2010  07:42:26
USER1   18.08.2010  04:25:48