CODEWITHSUNDEEP
phpmysqldatabase
Zubair1
Zubair1

Reputation: 2780

Storing Date Range in MySQL Solution

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.

Upvotes: 5

Views: 6628

Answers (4)

mellamokb
mellamokb

Reputation: 56769

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.

Upvotes: 6

TomaszSobczak
TomaszSobczak

Reputation: 2926

Why to complicate so much?

SELECT *
FROM `parking_lot_dates`
WHERE available_from_date <= '2012-04-22'
AND available_until_date >= '2012-04-30';

Upvotes: 3

Radu Murzea
Radu Murzea

Reputation: 10900

Using 1 column to store those dates is a bad design from a database point of view (not normalized). It's better to have 2 columns because the results can be retrieved easier and extracting the information from a single column would mean having to do some sort of split. It's just not elegant and it doesn't behave well when requirements change.

Upvotes: 0

bhooks
bhooks

Reputation: 419

I personally have found it better to have 2 columns, a start and end time, for searching a specific date, or just looking at it seems easier to me

Upvotes: 1

Related Questions