On 20 May 2010 19:36, Thom Brown <thombrown@xxxxxxxxx> wrote: > 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 > Actually, you could change that last bit from: OR ( extract(DOY from m.taken) >= day_of_year_start OR extract(DOY from m.taken) <= day_of_year_end ) to OR extract(DOY from m.taken) NOT BETWEEN day_of_year_end AND day_of_year_start That would be tidier and simpler :) Thom -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
