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
>
As well as the examples given in this NG already, if on 10g, look at
using the partitioned outer join capability for filling gaps in sparse
data - see
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10736/analysis.htm#DWHSG02013
Received on Wed Feb 15 2006 - 20:01:06 CST
