filling in missing dates in a time series
From: <carol_marra_at_msn.com>
Date: 15 Feb 2006 09:18:51 -0800
Message-ID: <1140023931.792056.149240_at_o13g2000cwo.googlegroups.com>
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.
carol Received on Wed Feb 15 2006 - 18:18:51 CET
Date: 15 Feb 2006 09:18:51 -0800
Message-ID: <1140023931.792056.149240_at_o13g2000cwo.googlegroups.com>
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 Received on Wed Feb 15 2006 - 18:18:51 CET