Re: filling in missing dates in a time series

<carol_marra_at_msn.com> a écrit dans le message de news: 1140024337.951380.247320_at_g43g2000cwa.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
|

You can outer join with something like that:

SQL> def beg_date=to_date('20/01/2006','DD/MM/YYYY')
SQL> def end_date=to_date('10/02/2006','DD/MM/YYYY')
SQL> alter session set nls_date_format='DD/MM/YYYY';

Session altered.

SQL> select &beg_date+rownum-1 "Date"
  2 from dual
  3 connect by level <= &end_date-&beg_date+1   4 /
Date

20/01/2006
21/01/2006
22/01/2006
23/01/2006
24/01/2006
25/01/2006
26/01/2006
27/01/2006
28/01/2006
29/01/2006
30/01/2006
31/01/2006
01/02/2006
02/02/2006
03/02/2006
04/02/2006
05/02/2006
06/02/2006
07/02/2006
08/02/2006
09/02/2006
10/02/2006

22 rows selected.

Regards
Michel Cadot Received on Wed Feb 15 2006 - 12:05:54 CST