Re: filling in missing dates in a time series

carol_marra_at_msn.com wrote:
> The bulk of our Oracle database data is time series data, at various
> intervals (hourly,
> daily, monthly, etc). When a value for a particular site is unavailable
> at a given
> timestep (for instance, if a sensor is down on March 1, 2005) we store
> *nothing*, rather than creating a record with a null value. Also note
> that we're not using any time-series management extensions to oracle.
> Timestamp on a value is stored as 2 oracle DATE fields,
> a start date and an end date, to indicate the entire interval to which
> the value applies.
>
> But, there are some instances where we want to display a complete,
> uninterrupted time series.... in other words, display those dates when
> there is no value actually stored in the database.
>
> To date, we have done this by outer joining with a table that holds all
> dates for the time step in question (hourly, daily, monthly, etc). It
> works, but it's not very elegant or practical.
>
> Other thoughts on how to achieve this? (Apologies if there are
> objections to this being posted in the theories group; it seems a
> reasonable place to me.)
>
> Thanks,
> carol
>

Oh, I forgot to mention in my other reply (which I admit didn't answer the question), and no offense to the question, but leaving theory aside I'm often suspicious of the usefulness of such applications without more information on the REAL business requirements. I've seen a few applications that resembled this and after talking to insiders found out that the requirements were basically invented by lazy programmers and analysts. Not saying that's the case here, but the businesses I've seen go out-of-business were often pre-occupied with what wasn't happening rather than with what was really happening!

pc Received on Wed Feb 15 2006 - 20:38:31 CET