I am working on script which requires giving the admin the ability to insert dates for when he wants a parking lot available, the admin inserts dates in a range.
I am having a hard time coming to a solution to what would be the best way to store the dates in MySQL.
Should i store the dates using two columns AVAILABLE_FROM_DATE
and AVAILABLE_UNTIL_DATE
?
PLID AVAILABLE_FROM DATE AVAILABLE_UNTIL_DATE
1 2012-04-01 2012-04-03
1 2012-04-05 2012-04-15
2 2012-04-21 2012-04-30
OR should i just use a single column AVAILABLE_DATE
and store the ranges the admin selects in a new row for each date between the range?
[EDIT START]
What i mean above by using a single column is not to join or split the dates into a single column, i actually mean to store a date in a single row with a single column like below:
PLID AVAILABLE_DATE
1 2012-04-01
1 2012-04-02
1 2012-04-03
and so on for all the available dates i want to store.
[EDIT END]
Basically, the admin will want to insert a date range the parking lot is available and allow members to choose that slot if the user is looking for a slot within that range.
OR is there some better and simpler way to do this?
I am currently trying to use the first method using separate columns for the range, but having trouble getting the desired results when looking for parking lots within a range.
[EDIT START]
SELECT * FROM `parking_lot_dates`
WHERE (available_from_date BETWEEN '2012-04-22' AND '2012-04-30'
AND (available_until_date BETWEEN '2012-04-22' AND '2012-04-30'))
I use the following query on the above rows i have, and it returns empty.
I want it to return the last row having the PLID 2.
[EDIT END]
Thank you in advance.
Regarding your EDIT with the query, you have the logic inside out. You need to compare whether each date you are checking is inside the range BETWEEN available_from_date and available_until_date
, like this:
SELECT * FROM `parking_lot_dates`
WHERE
(
'2012-04-22' BETWEEN available_from_date AND available_until_date
AND '2012-04-30' BETWEEN available_from_date AND available_until_date
)
Demo: http://www.sqlfiddle.com/#!2/911a3/2
Edit: Although if you'll want to allow partial-range matches, you'll need both types of logic, i.e., the parking lot is available 4-22 to 4-27, and you need it 4-23 to 4-28. You can use it for the dates 4-23 to 4-27, but not 4-28.
Why to complicate so much?
SELECT *
FROM `parking_lot_dates`
WHERE available_from_date <= '2012-04-22'
AND available_until_date >= '2012-04-30';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With