Thom Brown <thombrown@xxxxxxxxx> writes: > On 20 May 2010 17:36, David Jarvis <thangalin@xxxxxxxxx> wrote: > 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 > ) > ) extract(DOY) seems a bit problematic here, because its day numbering is going to be different between leap years and non-leap years, and David's problem statement doesn't allow for off-by-one errors. You could certainly invent your own function that worked similarly but always translated a given month/day to the same number. The other thing that's messy here is the wraparound requirement. Rather than trying an OR like the above (which I think doesn't quite work anyway --- won't it select everything?), it would be better if you can have the app distinguish wraparound from non-wraparound cases and issue different queries in the two cases. In the non-wrap case (start_day < end_day) it's pretty easy, just my_doy(m.taken) BETWEEN start_val AND end_val The easy way to handle the wrap case is my_doy(m.taken) <= start_val OR my_doy(m.taken) >= end_val although I can't help feeling there should be a smarter way to do this where you can use an AND range check on some modified expression derived from the date. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
