Re: filling in missing dates in a time series

carol_marra@xxxxxxx 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
.

Relevant Pages

  • Re: FP without FP extensions- what do I lose?
    ... it is only for a site that does *not* use FP server ... extensions or any of the components that are required to use extensions. ... Microsoft MVP - FrontPage ... |>> can't make an editable database? ...
    (microsoft.public.frontpage.extensions.windowsnt)
  • Re: Little design mistakes that can be easily avoided (2): Listenning to CELKO (and CELKO alikes)
    ... I disagree. ... non-key attributes leads to a less useful reading. ... There is no notion of time in a database except that which we model. ... one of the possible extensions since only one possible extension can be ...
    (comp.databases.theory)
  • Re: Emailed word file got a quicktime
    ... of the past preceded with the extensions. ... central database called the LaunchService database. ... Having the app on your Mac is not always ...
    (microsoft.public.mac.office.word)
  • Re: SQL
    ... > database-vendor-specific extensions, such as stores procedures, triggers, ... but none of that is part of the core of what a database does. ... > There's a reason why all these bolt-ons have materialized: database engine ...
    (comp.object)
  • Re: Coextensive properties?
    ... > extensions, there's no reason why one can't introduce one. ... place predicate" and also exclude those predicates that include ... propositions, properties or concepts to extensional ... he has no misgivings with intensions. ...
    (sci.logic)