On Oct 15, 2007, at 10:50 , brian wrote:
Adjust your data so that table agri_area has a row for each year?
I can imagine that some of his raw data is a multi-year aggregate, so it may be difficult for him to do this.
Otherwise, i think you'd need to parse your year column by expanding any entries with a dash into a range.
I suggest using two *date* (or possibly integer) columns for each row and consider each row an year interval (in the mathematical sense, not to be confused with SQL intervals, which are actually durations). Depending on the interval representation you choose (closed-open or closed-closed), a single-year interval (say, 1970), would be represented as either ('1970-01-01', '1970-01-01') or ('1970-01-01', '1971-01-01').
The reason I suggest using dates rather than integers is that it gives you more flexibility for the future, and you have access to all of the date functions available in Postgres that you will probably find useful.
But i'm not sure that you could then compare a range against a list (WHERE ... IN ... )
The only reference for managing temporal data in ANSI SQL I'm aware of is Snodgrass' "Developing Time-Oriented Database Applications in SQL"[1]. Date, Darwen, and Lorentzos' "Temporal Data and the Relational Model"[2] is a more general text on the same topic, but isn't directly applicable to ANSI SQL. I suggest looking at least at the first.
Hope this helps. Michael Glaesemann grzm seespotcode net [1](http://www.cs.arizona.edu/~rts/tdbbook.pdf) [2](http://www.amazon.com/gp/product/1558608559/) ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
