Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> filling in missing dates in a time series
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 - 11:25:38 CST
![]() |
![]() |