Hi,
check (taken >= '1913-12-01' and taken <= '1913-12-31')
I don't think I want to constrain by year, for a few reasons:
1. There are a lot of years -- over 110.
2. There will be more years added (both in the future for 2010 and in the past as I get data from other sources).
Currently I have it constrained by month and category. Each table then has about 3 million rows (which is 216 million, but some tables have more, which brings it to 273 million).
/* Data before 1900 is shaky; insufficient after 2009. */
-- I have no idea why this is here.. Aren't you forcing
Mostly temporary. It is also constrained by the user interface; however that will likely change in the future. It should not be present in the database structure itself.
/* Between the selected days and years... */
CASE
WHEN (user_start_year || user_start_day <= user_stop_year || user_stop) THEN
m.taken BETWEEN user_start_year || user_start_day AND user_stop_year || user_stop
WHEN (user_start_year || user_start_day > user_stop_year || user_stop) THEN
m.taken BETWEEN (user_start_year || user_start_day)::date AND
((user_stop_year || user_stop)::date + '1
year'::interval)::date
-- I don't think you need/want this..?
User selects this:
1. Years: 1950 to 1974
2. Days: Dec 22 to Mar 22
This means that the query must average data between Dec 22 1950 and Mar 22 1951 for the year of 1950. For 1951, the range is Dec 22 1951 to Mar 22 1952, and so on. If we switch the calendar (or alter the seasons) so that winter starts Jan 1st (or ends Dec 31), then I could simplify the query. ;-)
Dave