PostgreSQL stuff

Problem: Generating a nice report grouped by date while some dates are missing data. (I admit the topic indicates something a little bit more exciting...)

Previously I have solved this kind of problem in the client code or by having some sort of calendar table to join with. This time I don't have any client code (yet) and I have no other tables to use. Lucky me I'm using PostgreSQL since it has the generate_series function.

So basically all I have to do is a RIGHT OUTER JOIN with generate_series as dates:

select date '2009-02-01' + generate_series(0,6);

An full example to play with:

drop table if exists mytable;
create table mytable(id serial, day date,     metric int);
insert into mytable(day, metric) values('2009-02-01', random()*10),
('2009-02-01', random()*10), ('2009-02-01', random()*10),
('2009-02-03', random()*10), ('2009-02-03', random()*10),
('2009-02-03', random()*10), ('2009-02-03', random()*10),
('2009-02-04', random()*10), ('2009-02-04', random()*10),
('2009-02-05', random()*10), ('2009-02-05', random()*10),
('2009-02-05', random()*10), ('2009-02-05', random()*10),
('2009-02-07', random()*10), ('2009-02-07', random()*10);

Now try it out:

select
    coalesce(mytable.day, foo.day) as day,
    coalesce(mytable.sum, foo.sum) as sum
from
    (select day, sum(metric)
     from mytable group by day) as mytable
right outer join
    (select date '2009-02-01' + foo.day      as day, 0 as sum
     from generate_series(0,6) as foo(day)) as foo
on mytable.day=foo.day
order by day;

As stated earlier I have usually solved this problem in client code. I recall doing so both when using MSSQL and Oracle although I haven't worked with them for a long while now. I can't recall ever having done any similar reports while using MySQL.

I'm curious about the current state of Oracle, MSSQL and MySQL since I'm not actively using them. Is there something similar as generate_series? What about DB2?