Date comparison without a year

From: Stanislav Orlenko <orlenko(dot)stas(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Date comparison without a year
Date: 2010-10-19 20:26:24
Message-ID: 1287519984.9073.31.camel@marv.site
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello
There is a table with user reviews about hotels, every row has columns:
time_of_travel_begin and time_of_travel_end. User can select date range
(interval), for example 15 June - 10 July and DB request should return
all reviews for any years (for 15 June - 10 July 2010, 15 June - 10 July
2009 etc). For now I've constructed this request:

SELECT *
FROM comments
WHERE
hotel_id = 4323
AND
(
TO_DATE(TO_CHAR(time_of_travel_begin, 'MM-DD'), 'MM-DD')
BETWEEN TO_DATE('06-15', 'MM-DD') AND TO_DATE('07-10', 'MM-DD')
OR
TO_DATE(TO_CHAR(time_of_travel_end, 'MM-DD'), 'MM-DD')
BETWEEN TO_DATE('06-15', 'MM-DD') AND TO_DATE('07-10', 'MM-DD')
OR
(TO_DATE(TO_CHAR(time_of_travel_begin, 'MM-DD'), 'MM-DD') <
TO_DATE('06-15', 'MM-DD') AND TO_DATE(TO_CHAR(time_of_travel_end,
'MM-DD'), 'MM-DD') > TO_DATE('07-10', 'MM-DD'))
)

and this select works.

But if first date of interval is in December and the second date of
interval is in January (for example interval 20 December - 10 January) -
it's a problem. Select will be looks like:

SELECT *
FROM comments
WHERE
hotel_id = 4323
AND
(
TO_DATE(TO_CHAR(time_of_travel_begin, 'MM-DD'), 'MM-DD')
BETWEEN TO_DATE('12-20', 'MM-DD') AND TO_DATE('01-10', 'MM-DD')
OR
TO_DATE(TO_CHAR(time_of_travel_end, 'MM-DD'), 'MM-DD')
BETWEEN TO_DATE('12-20', 'MM-DD') AND TO_DATE('01-10', 'MM-DD')
OR
(TO_DATE(TO_CHAR(time_of_travel_begin, 'MM-DD'), 'MM-DD') <
TO_DATE('12-20', 'MM-DD') AND TO_DATE(TO_CHAR(time_of_travel_end,
'MM-DD'), 'MM-DD') > TO_DATE('01-10', 'MM-DD'))
)

this select returns wrong results.

How can I retrieve all reviews from 20 Dec to 10 Jan for any year?

Thanks in advance

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2010-10-19 20:39:58 Re: Database INNOVATION
Previous Message Ashesh Vashi 2010-10-19 19:42:21 Re: install PostgreSQL 9 .0.1 issue using linux binary package .bin