Oracle中同一列中的两个日期之间的差异

Question

我有一个表 MyTable ，其中包含 columns:

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

和 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

我如何得到这样的内容：

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 

试试这个。

Answer 2

/* CREATING TABLE */
create table T_USER_INCIDENT
(
ID number
,STAT number(1)
,USER_ID varchar2(15)
,INCIDENT_DATE date
)
/*================================*/

/* CREATING SEQENCE */
create sequence SQ_USER_INCIDENT
start with 1 
increment by 1 
nomaxvalue;
/*================================*/

/* CREATE TRIGGER FOR ID INSERT */
create trigger TRIG_USER_INCIDENT
before insert on T_USER_INCIDENT
for each row
begin
select SQ_USER_INCIDENT.nextval into :new.id from dual;
end;
/
/*================================*/

/* INSERTING GIVEN SAMPLE  DATA */
INSERT INTO T_USER_INCIDENT (STAT,USER_ID,INCIDENT_DATE) VALUES (1,'USER1',TO_DATE('18.08.2010 13:10:14','DD.MM.YYYY HH24:MI:SS'));
INSERT INTO T_USER_INCIDENT (STAT,USER_ID,INCIDENT_DATE) VALUES (2,'USER1',TO_DATE('18.08.2010 15:15:40','DD.MM.YYYY HH24:MI:SS'));
INSERT INTO T_USER_INCIDENT (STAT,USER_ID,INCIDENT_DATE) VALUES (1,'USER1',TO_DATE('18.08.2010 16:15:10','DD.MM.YYYY HH24:MI:SS'));
INSERT INTO T_USER_INCIDENT (STAT,USER_ID,INCIDENT_DATE) VALUES (2,'USER1',TO_DATE('18.08.2010 18:35:32','DD.MM.YYYY HH24:MI:SS'));
INSERT INTO T_USER_INCIDENT (STAT,USER_ID,INCIDENT_DATE) VALUES (1,'USER2',TO_DATE('18.08.2010 07:05:46','DD.MM.YYYY HH24:MI:SS'));
INSERT INTO T_USER_INCIDENT (STAT,USER_ID,INCIDENT_DATE) VALUES (2,'USER2',TO_DATE('18.08.2010 13:10:01','DD.MM.YYYY HH24:MI:SS'));
INSERT INTO T_USER_INCIDENT (STAT,USER_ID,INCIDENT_DATE) VALUES (1,'USER1',TO_DATE('19.08.2010 09:01:10','DD.MM.YYYY HH24:MI:SS'));
INSERT INTO T_USER_INCIDENT (STAT,USER_ID,INCIDENT_DATE) VALUES (2,'USER1',TO_DATE('19.08.2010 16:15:19','DD.MM.YYYY HH24:MI:SS'));
INSERT INTO T_USER_INCIDENT (STAT,USER_ID,INCIDENT_DATE) VALUES (1,'USER2',TO_DATE('19.08.2010 11:02:56','DD.MM.YYYY HH24:MI:SS'));
INSERT INTO T_USER_INCIDENT (STAT,USER_ID,INCIDENT_DATE) VALUES (2,'USER2',TO_DATE('19.08.2010 18:45:22','DD.MM.YYYY HH24:MI:SS'));
/*================================*/

/* CHECKING INSERTED DATA */
SELECT * FROM T_USER_INCIDENT

/*===================================================*/
/*               OUT PUT OF ABOVE QUERY              */
/*===================================================*/
/* 1    1   1   USER1   8/18/2010 1:10:14 PM */
/* 2    2   2   USER1   8/18/2010 3:15:40 PM */
/* 3    3   1   USER1   8/18/2010 4:15:10 PM */
/* 4    4   2   USER1   8/18/2010 6:35:32 PM */
/* 5    5   1   USER2   8/18/2010 7:05:46 AM */
/* 6    6   2   USER2   8/18/2010 1:10:01 PM */
/* 7    7   1   USER1   8/19/2010 9:01:10 AM */
/* 8    8   2   USER1   8/19/2010 4:15:19 PM */
/* 9    9   1   USER2   8/19/201011:02:56 AM */
/* 10   10  2   USER2   8/19/2010 6:45:22 PM */
/*===================================================*/

/*CREATING FUNCTION TO RETURN RESPECTED CLOSING TIME OF INCIDENT WITH GIVEN USER_ID */
CREATE OR REPLACE FUNCTION FN_GET_INCIDENT_END_TIME
(P_USER_ID IN T_USER_INCIDENT.USER_ID%TYPE,
 P_INCIDENT_START_TIME T_USER_INCIDENT.INCIDENT_DATE%TYPE)
RETURN T_USER_INCIDENT.INCIDENT_DATE%TYPE
IS
  V_INCIDENT_CLOSE_TIME T_USER_INCIDENT.INCIDENT_DATE%TYPE;
BEGIN
     SELECT MIN(CLOSE_TIMES)
     INTO V_INCIDENT_CLOSE_TIME
     FROM
     (SELECT A.INCIDENT_DATE AS CLOSE_TIMES
      FROM T_USER_INCIDENT A
      WHERE A.USER_ID=P_USER_ID AND A.INCIDENT_DATE>P_INCIDENT_START_TIME AND A.STAT=2);
      RETURN V_INCIDENT_CLOSE_TIME;
END FN_GET_INCIDENT_END_TIME;
/*================================*/

/* MAIN QUERY FOR OUT PUT */
SELECT USER_ID as "USER",TRUNC(INCIDENT_DATE) as "DATE",to_char(to_date('00:00:00','HH24:MI:SS') + SUM(DURATION), 'HH24:MI:SS') as "DURATION"
FROM
(SELECT
UI.USER_ID
,UI.INCIDENT_DATE
,FN_GET_INCIDENT_END_TIME(UI.USER_ID,UI.INCIDENT_DATE) AS END_TIME
,NVL(FN_GET_INCIDENT_END_TIME(UI.USER_ID,UI.INCIDENT_DATE),SYSDATE)-UI.INCIDENT_DATE AS DURATION
FROM T_USER_INCIDENT UI
WHERE STAT=1)
GROUP BY TRUNC(INCIDENT_DATE),USER_ID

/*================================*/
/*=======================================*/
/*         OUT PUT OF ABOVE QUERY        */
/*=======================================*/
/* 1    USER1   8/18/2010   04:25:48 */
/* 2    USER2   8/18/2010   06:04:15 */
/* 3    USER1   8/19/2010   07:14:09 */
/* 4    USER2   8/19/2010   07:42:26 */
/*=======================================*/

Answer 3

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

输出

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