MySQL Dates Between Dates

Question

I am wondering if there is a way in MySQL 4.1+ to track specific dates between date ranges in my records?

For a quick example, I have two records with date-time ranges, for example

ID | Activity | Start Date | End Date 1 Closed 1/1/11 11:00:00 1/4/11 11:00:00 2 Open 1/15/11 10:00:00 1/19/11 09:00:00 

I want to know if there is a way to get dates between “Start Date” and “End Date” for each of these entries? For instance:

1/2/11, 1/3/11

and

1/16/11, 1/17/11, 1/18/11

Or at least a way to approach this and use PHP to end the rest of the way? Thanks!

Answer 1

Jordan,

This is not possible directly with MySQL 4. And I think that this logic should not be placed in your database level.

However, there is a problem with StackOverflow that is related to your problem, and the solution is a stored procedure (but you need mysql 5 for this). View it here: Get a list of dates between two dates

I found not ready to use, but a reasonable approach to your problem in PHP on this web page: http://prajapatinilesh.wordpress.com/2009/05/07/get-all-dates-between-two-dates-using -php-code /

Hope this helps you in the right direction.

Answer 2

This may solve your problem:

 SELECT * FROM `your_table` WHERE start_date > '2011-01-01' AND end_date < '2011-01-04' 

Answer 3

I have included this in my own mysql queries before.

 SELECT id FROM table WHERE some_date BETWEEN start_date AND end_date