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
>

Can't comment on products. If you're talking about the recording of negatives in RM theory, the wiki page at http://en.wikipedia.org/wiki/Relational_algebra has lately undergone some enhancement by several of the RM heavyweights.

One paragraph that I found enlightening is:

(quote):

It is important to realise that Codd's algebra is not in fact complete with respect to first-order logic. Had it been so, certain insurmountable computational difficulties would have arisen for any implementation of it. To overcome these difficulties, he restricted the operands to finite relations only and also proposed restricted support for negation (NOT) and disjunction (OR). Analogous restrictions are found in many other logic-based computer languages. Codd defined the term relational completeness to refer to a language that is complete with respect to first-order predicate calculus apart from the restrictions he proposed. In practice the restrictions have no adverse effect on the applicability of his relational algebra for database purposes.

(end of quote).

Personally, I'm not sure about "no adverse effect". As I understand it, Codd was talking about recording facts through what are called their extensions, basically the enumerations of sets, possibly very lengthy enumerations as you seem to be referring to. The logicians/mathematicians have a counterpart (I believe) that they call an intension which is a description of a set that is often much more concise in its expression than is an extension. It seems that the general understanding of the place of intensions is in programs as opposed to databases. If I'm right that that's the orthodox way to view things, then the theory would agree with your opinion that the 'all-dates' table is a clumsy solution.

Wistfully, I find this a bit unfortunate, but maybe I just don't know enough to see that an RM with intensions is a bigger problem, eg., a 'table' defined via intension. The RM people seem to accept or even popularize the idea of a constraint on extensions (apart from 'integrity' reasons, this can 'limit' tables to some practical size).

     If extensions can have constraints, why not allow intensions since they are effectively constraints on a domain?

Some of the 'OO database' activity seems to have been perhaps indirectly motivated by how to approach negatives with high-level support for defining domains, although there doesn't seem to be much solid theory behind it, just ease-of-programming arguments which are often disputed.   The big mistake of their proponents seems to be that in trying this they also discarded the RM!

Likely this is an unsatisfactory answer to anybody who wants a more 'elegant' solution right now.

pc Received on Wed Feb 15 2006 - 20:06:35 CET