Home › select time intervals in sql

Reply to comment

select time intervals in sql

Tue, 03/24/2009 - 17:36 — 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

Reply

The content of this field is kept private and will not be shown publicly.
CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
H
g
W
c
e
8
Enter the code without spaces and pay attention to upper/lower case.