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
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 |