select time intervals in sql

Date Tags sql

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

sql insert using a select and a static value

Date Tags sql

This is just for reference. I forgot the syntax already twice. The insert query does not work if you use parenthesis.

create table test ( a int , b int) ;
create table test1 ( c int) ;
insert into test1 values 1;
insert into test1 values 2;
insert into test1 values 3;
insert into test1 values 4;

insert into test (a,b) select c,0 from test1;
select * from test;
1|0
2|0
4|0
3|0