Home

select time intervals in sql

24 March 2009 - 5:36pm — abate

consider the following example :

CREATE TABLE elem (
    id INTEGER,
    name VARCHAR,
    num INTEGER
);
CREATE TABLE proxy (
    elem_id INTEGER,
    time_id INTEGER
);
CREATE TABLE TIME (
    id INTEGER,
    TIMESTAMP DATESTAMP
);

INSERT INTO elem VALUES (0,"one",1);
INSERT INTO elem VALUES (1,"one",2);
INSERT INTO elem VALUES (2,"one",3);
INSERT INTO elem VALUES (3,"two",1);

INSERT INTO proxy VALUES (0,0);
INSERT INTO proxy VALUES (0,1);
INSERT INTO proxy VALUES (1,2);
INSERT INTO proxy VALUES (1,3);
INSERT INTO proxy VALUES (2,4);
INSERT INTO proxy VALUES (3,4);

INSERT INTO TIME VALUES (0,"12/12/2008");
INSERT INTO TIME VALUES (1,"13/12/2008");
INSERT INTO TIME VALUES (2,"14/12/2008");
INSERT INTO TIME VALUES (3,"15/12/2008");
INSERT INTO TIME VALUES (4,"16/12/2008");

What I want is to write a query that will group for each (name,num) the start and end edges of the interval given by the table time:

For example:

one | 1 | 12/12/2008 | 13/12/2008
one | 2 | 14/12/2008 | 15/12/2008
one | 3 | 16/12/2008 | 16/12/2008
two | 1 | 16/12/2008 | 16/12/2008

First we create a simple view to unclutter the query statement.

 CREATE temp VIEW all_t AS SELECT elem.name,elem.num,TIME.TIMESTAMP FROM elem,TIME,proxy WHERE proxy.elem_id = elem.id AND TIME.id = proxy.time_id ;

Then the sql query is pretty straightforward ...

SELECT name,num,MIN(TIMESTAMP),MAX(TIMESTAMP) FROM all_t GROUP BY name, num;
one|1|12/12/2008|13/12/2008
one|2|14/12/2008|15/12/2008
one|3|16/12/2008|16/12/2008
two|1|16/12/2008|16/12/2008

Average: 1.3 (6 votes)