On 20 May 2010 17:36, David Jarvis <thangalin@xxxxxxxxx> wrote: > Hi, Thom. > > The query is given two items: > > Range of years > Range of days > > I need to select all data between the range of days (e.g., Dec 22 - Mar 22) > over the range of years (e.g., 1950 - 1970), such as shown here: > > http://i.imgur.com/MUkuZ.png > > For Jun 1 to Jul 1 it would be no problem because they the same year. But > for Dec 22 to Mar 22, it is difficult because Mar 22 is in the next year > (relative to Dec 22). > > How do I do that without strings? > > Dave > > Okay, get your app to convert the month-date to a day of year, so we have year_start, year_end, day_of_year_start, day_of_year_end and your where clause would something like this: WHERE extract(YEAR from m.taken) BETWEEN year1 and year2 AND ( extract(DOY from m.taken) BETWEEN day_of_year_start AND day_of_year_end OR ( extract(DOY from m.taken) >= day_of_year_start OR extract(DOY from m.taken) <= day_of_year_end ) ) ... substituting the placeholders where they appear. So if we had: year1=1941 year2=1952 day_of_year_start=244 (based on input date of 1st September) day_of_year_end=94 (based on 4th April) We'd have: WHERE extract(YEAR from m.taken) BETWEEN 1941 and 1952 AND ( extract(DOY from m.taken) BETWEEN 244 AND 94 OR ( extract(DOY from m.taken) >= 244 OR extract(DOY from m.taken) <= 94 ) ) Then you could add expression indexes for the YEAR and DOY extract parts, like: CREATE INDEX idx_taken_doy ON climate.measurement (EXTRACT(DOY from taken)); CREATE INDEX idx_taken_year ON climate.measurement (EXTRACT(YEAR from taken)); Although maybe you don't need those, depending on how the date datatype matching works in the planner with the EXTRACT function. Regards Thom -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
