Re: Select when table have missing data

On 2 Mar 2007 01:17:33 -0800, Lars Gregersen <lars(dot)gregersen(at)it(dot)dk> wrote:
> I have a table that contains historical exchange rates:
> date_time | timestamp
> xrate | real
>
> There is a maximum of one entry per day, but data are missing on
> weekends and holidays. For these missing dates I must use the value
> from the previous day (e.g. since data for a Sunday is missing I must
> use the value from the Friday just before the weekend).
>
> I have two questions:
>
> 1) Since historical exchange rates are not supposed to change I
> thought about creating a new table with data for all the missing dates
> (calculated using some procedural language). However, I would be much
> happier if there was a way to do this using SQL in a SELECT statement.
> Do you have any hints for this?
>
> 2)
> I have a number of other tables where data may be missing for
> different reasons. These data may be on a daily or an hourly basis.
> When a user selects a range of data from e.g. the 1st of January to
> the 1st of February I would like to be able to return a full set of
> data where all missing entries are returned as NULL. Is there a smart
> way to do this using SQL?
>
> Any hints or references you may have on the subject of handling
> missing data in time series data are very welcome.
>
> If there is a smarter way to set up tables for handling this type of
> data then please enlighten me.
>
> Thanks
>
> Lars

generate_series() is your friend:

------------------------------

create table t(
date_time timestamp
, xrate real
);

insert into t values ('2007-3-7',0.23);
insert into t values ('2007-3-8',0.1);
insert into t values ('2007-3-9',0.2);
-- no '2007-3-10'
-- no '2007-3-11'
insert into t values ('2007-3-12',0.3);
insert into t values ('2007-3-13',0.4);
-- no '2007-3-14'
insert into t values ('2007-3-15',0.99);
-- no '2007-3-16'

select d.*, (
select xrate
from t
where date_time = (
select max(date_time)
from t
where date_time <= d.ddate
)
) as xrate
from (
select ('2007-3-7'::date+s.x)::timestamp as ddate
from generate_series(0,9) s(x)
) d

------------------------------

Extrapolate for case 2.

Regards.