MySQL Dates between Dates

Question

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

For a quick example, I have two records with datetime ranges, like

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

What I want to know is, is there a way I can get the dates between the "Start Date" and "End Date" for each of these records? Like so:

1/2/11, 1/3/11

And

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

Or at least a way to get close to that and use PHP to finish the rest of the way? Thanks!

What do you want to do with the date? Get records between them, or just the dates themselves between start and end? — gen_Eric, Jan 06 '11 at 22:40
This is all on one table. I would like to get the day, month, year at the very least. I actually want to exclude the days themselves--since the event occurs by the hour, the day itself is not effected by the event (like, if we close up for 6 hours one day, but are open for the first 4 hours of that day.) — Jordan, Jan 06 '11 at 22:48

score 1 · Answer 1 · answered Jan 06 '11 at 22:36

1

This might sole your problem:

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

answered Jan 06 '11 at 22:36

Diablo

3,378
1
22
28

score 0 · Accepted Answer · edited May 23 '17 at 11:47

0

Jordan,

This is not directly possible with MySQL 4. And i think this logic should not be put in your database layer.

There is however a question on StackOverflow that relates to your problem and the solution is a stored procedure (but for that you need mysql 5). View it here: Get a list of dates between two dates

I found a not ready to use, but sensible approach to your problem in PHP at 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.

edited May 23 '17 at 11:47

Community

1
1

answered Jan 06 '11 at 22:44

Richard Tuin

4,484
2
19
18

Thanks, I'll take a look. I don't mind using a PHP solution, but I want to get the most out of the query if possible. – Jordan Jan 06 '11 at 22:50

score 0 · Answer 3 · answered Jan 06 '11 at 23:01

0

I have implimented this in my own mysql queries before.

SELECT id FROM table WHERE some_date BETWEEN start_date AND end_date

answered Jan 06 '11 at 23:01

dqhendricks

19,030
11
50
83

MySQL Dates between Dates

3 Answers3