DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=bj6ihSjWlcLw+8d2/DJYf1leU/7pBxq0n678jZxIlQymeXz8oboeBLghTf8hJOboRyErcJ3/8cvWE5ScwL198LmtYC9Gg/Z6DoyPjP1xlvM/WdkHoAe2+PPg1z7wc9KrC5LmKN1pXceVtP5/fEejW9JY8LrUrHssHRzrRSN2/ug=
Message-ID: <a55915760703081730x44817d50k1a1c8b501be0ab7d@mail.gmail.com>
Date: Thu, 8 Mar 2007 20:30:26 -0500
From: "=?ISO-8859-1?Q?Rodrigo_De_Le=F3n?=" <rdeleonp@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Select when table have missing data
In-Reply-To: <1172827053.569467.79430@p10g2000cwp.googlegroups.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <1172827053.569467.79430@p10g2000cwp.googlegroups.com>

On 2 Mar 2007 01:17:33 -0800, Lars Gregersen <lars.gregersen@it.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.