Hi,
you can create a table with sequence of dates,
and fill it with a procedure,
like this:
create table mtab(
date datetime,
value varchar(100)
);
insert into mtab
select '2012-02-15 00:00:00' , 'a' union
select '2012-02-15 01:00:00' , 'b' union
select '2012-02-15 03:00:00' , 'd' union
select '2012-02-15 04:00:00' , 'e'
;
create table dat_seq(
dt datetime primary key
);
delimiter $$
drop procedure if exists fill_sequence_table $$
create procedure fill_sequence_table( mindate datetime, maxdate datetime )
begin
declare hours int;
declare i int;
truncate table dat_seq;
set hours = timestampdiff(hour, mindate, maxdate);
set i = 0;
while hours >= 0 do
insert into dat_seq values( timestampadd( hour, i, mindate ));
set hours = hours - 1;
set i = i + 1;
end while;
end;
$$
delimiter ;
call fill_sequence_table( ( select min( date ) from mtab ),
( select max( date ) from mtab )
);
then use this simple query:
select date,
case when value is null then @lastvalue
else (@lastvalue := value )
end value
from (
select ds.dt date, mt.value
from dat_seq ds
left join mtab mt
on ds.dt = mt.date
order by ds.dt
) x
cross join (
select (@lastvalue:=value)
from mtab
order by date limit 1
) dummy
;
+---------------------+-------+
| date | value |
+---------------------+-------+
| 2012-02-15 00:00:00 | a |
| 2012-02-15 01:00:00 | b |
| 2012-02-15 02:00:00 | b |
| 2012-02-15 03:00:00 | d |
| 2012-02-15 04:00:00 | e |
+---------------------+-------+
5 rows in set (0.00 sec)