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?
- 2So, 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– Anil2015-02-02 20:05:31 +00:00Commented Feb 2, 2015 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– ClarkeyBoy2015-02-02 20:12:00 +00:00Commented Feb 2, 2015 at 20:12
- @SlyRaskal, I've tried billions of different where statements, I just didn't know which to post.sveatlo– sveatlo2015-02-02 20:14:31 +00:00Commented Feb 2, 2015 at 20:14
Add a comment |
3 Answers
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)) - First part of the match: stay_from_date must be in the range of the report.
- Second part of the match: stay_to_date must be before the end date of the report.
- All $variables are mysql DATETIME types = 2011-09-21 08:21:22, see MySQL reference: http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_date
5 Comments
ClarkeyBoy
+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.
Brian
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.
ClarkeyBoy
Don't mean to be picky, and maybe I'm going blind, but I only see an
and.ClarkeyBoy
Sorry, just realised you didn't post this answer but the other one. I've upvoted both.
Jens A. Koch
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 ;)
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.
1 Comment
Jens A. Koch
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.
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