Storing time intervals

[spencerkclark]

Something that has yet to be implemented is storing the time interval of a calculation in the database. This could be done in a very basic form by just storing string representations of the start date and end date in separate columns.

Before implementing something like this though, one thing that we'd have to think about is what this would mean for making queries. What kinds of queries would we be making that depended on the dates?

When looking for time series computed data for instance (in the spirit of aospy issue 3), all that matters is that the datapoints you are looking for lie within the time interval for which datapoints exist. So rather than having to supply the exact dates for which the time series began and ended, we should be able to supply a range of any dates, and query the database for a computation that has data in that time frame. Unfortunately sqlite does not support a DATETIME type to query upon; however we may be able to come up with a String or Float-type format for the dates that would enable this sort of general querying.

[spencerahill]

Unfortunately sqlite does not support a DATETIME type to query upon; however we may be able to come up with a String or Float-type format for the dates that would enable this sort of general querying.

Is SQLite the definite way forward? If yes, it would be worth determining precisely how it handles these date/string conversions internally (c.f. your above link to SQLAlchemy: "In the case of SQLite, date and time types are stored as strings which are then converted back to datetime objects when rows are returned."). And then just do the same? Or are they saying that SQLAlchemy does the conversions for you?

Before implementing something like this though, one thing that we'd have to think about is what this would mean for making queries. What kinds of queries would we be making that depended on the dates?

I'm probably missing something, but regardless of queries, we need to store the precise dates over which something is computed, no? For the sake of having complete metadata about what a quantity represents.

[spencerkclark]

Good catch! I didn't realize SQLAlchemy does what I was describing for us. I implemented this in the most recent commit, 3c618af.