CODEWITHSUNDEEP
phpmysql
sveatlo
sveatlo

Reputation: 593

Find all in dates range

I've got a MySQL table of stays, where each stay is defined with a from_date and to_date column. And I want to find all stays in range defined by user. For example, given that there is a stay from 01.01.2015 to 01.03.2015 I want this entry to be included in reports from 01.01.2015 to 31.01.2015 but also from 01.02.2015 to 28.02.2015 and in 01.03.2015 to 31.03.2015. I've got everything stored as timestamps (in seconds). Could somebody, please, give an example how to achieve this?

Upvotes: 0

Views: 74

Answers (3)

sveatlo
sveatlo

Reputation: 593

Ok, thanks to the other answers I got the best results with (stay_range_start<=selected_range_end) && (stay_range_end>=selected_range_start) in mysql:

SELECT * FROM stays
WHERE  `t0`.`from_date` <= $to_date 
AND `t0`.`to_date` >= $from_date

Upvotes: 1

Brian
Brian

Reputation: 346

You can use BETWEEN to select a date between two values, and combine it with an OR so you select either the from date or the to date:

SELECT * FROM your_table 
WHERE (from_date BETWEEN 'date_1' AND 'date_2')
OR (to_date BETWEEN 'date_1' AND 'date_2');

See this related answer for more information, and keep in mind you will need to convert the user input dates to timestamps, or convert your timestamps to dates for the comparison.

Upvotes: 1

Jens A. Koch
Jens A. Koch

Reputation: 41756

Abstract MySQL query:

select *
from table_of_stays
where ( date($stay_from_date) 
        between date($report-start-date) and date($report-end-date)) 
and (date($stay_to_date) < date($report-end-date))

Upvotes: 1

Related Questions