0

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?

sveatlo
  • 543
  • 9
  • 27
  • 2
    So, what query, or queries, have you tried writing so far to get this going? This site isn't focused on giving people free code. It's focus is on helping you resolve specific issues, with existing code that you already have written. Cheers. – Anil Feb 02 '15 at 20:05
  • Don't know about anyone else but for me comparing a timestamp column to dates works fine. In MySQL Workbench this datatype actually displays as a date anyway so, unless MySQL Workbench does anything fancy to make it more humanly readable, I expect it is stored as such. Make sure you use the universal format - yyyy-mm-dd - otherwise it can get confusing. – ClarkeyBoy Feb 02 '15 at 20:12
  • @SlyRaskal, I've tried billions of different where statements, I just didn't know which to post. – sveatlo Feb 02 '15 at 20:14

3 Answers3

1

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.

Community
  • 1
  • 1
Brian
  • 336
  • 1
  • 9
  • A downvote means that the answer is not useful. I don't know why someone found your answer not useful. The answer points in the right direction. Seems to be a recent trend on SO to vote everything down. – Jens A. Koch Feb 02 '15 at 20:16
1

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))
Jens A. Koch
  • 39,862
  • 13
  • 113
  • 141
  • 1
    +1 but this isn't *quite* what was asked for. It is along the right track. It should be that the start date or the end date are between the report dates - .i.e. start date is between from date and to date and/or end date is between from and to date. Your answer would return those where the stay starts between the from and to date and ends before the end date. – ClarkeyBoy Feb 02 '15 at 20:17
  • Which is why I used an OR in my answer - either the from_date OR the to_date may be within the range; it is not required for both of them to be. – Brian Feb 02 '15 at 20:19
  • 1
    Don't mean to be picky, and maybe I'm going blind, but I only see an `and`. – ClarkeyBoy Feb 02 '15 at 20:19
  • Sorry, just realised you didn't post *this* answer but the other one. I've upvoted both. – ClarkeyBoy Feb 02 '15 at 20:20
  • ClarkeyBoy, good catch here. This will only select inside the range and not outside. The OR might be useful. The questioner solved it by using a simple in range comparison <= and >= - o.O Enough ideas around to get close to a solution ;) – Jens A. Koch Feb 02 '15 at 20:42
1

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
sveatlo
  • 543
  • 9
  • 27