PHP and MySQL: Comparing month and day with dynamic year

Question

I am having a bit of trouble with an application I am working on and I hope to get some guidance.

I have a simple booking system: each item has a standard price, but there are some periods when the price can change.

For example:

• Standard price is 100/day

• From Oct. 1st to Feb. 1st of each year, the price is 80/day

• From Jun. 1st to Aug. 31st of each year the price is 120/day

• [...] up to 6 different price changes (seasons)

Because this is a yearly occurrence, I only need the month and day, but I dont't know how to store these values in the database. I was thinking of having two fields, one for month and one for day which hold numeric values(01-12 for month, 01-31 for day). These values are set by the user himself and they are relative to the item itself, it's not fixed for everyone.

The problem is that when someone makes a booking, I need to check if the price needs to be changed and I don't understand how to build the query to check this. To make maters worst, the price can have multiple values for a booked period.

Following the example above, If I were to book an item from Sep. 1st (current year) to Jul. 1st (next year), the price will be as follow:

• 100/day from Sep. 1st to Sep. 30th

• 80/day from Oct. 1st to Feb. 1st

• 100/day from Feb. 2nd to May 31st

• 120/day from Jun. 1st to Jul. 1st

Is there any way of checking these cases without having to loop each day and checking against the database for a match ?

Thank you

Answer 1

I don't know your database tables, but I think you could do like this:

1. Get your period, iniDay, iniMonth, endDay, endMonth
2. Your table need to have these fields: ini_day, ini_month, ini_daymonth, end_day, end_month, end_daymonth, price_day
3. SELECT * from table WHERE iniDay >= booking_ini_day AND iniMonth >= booking_ini_month -- get initial period AND iniDay <= booking_end_day AND iniMonth <= booking_end_month UNION ALL SELECT * from table WHERE iniDay <= booking_ini_day AND iniMonth <= booking_ini_month -- get middle periods AND endDay >= booking_end_day AND endMonth >= booking_end_month UNION ALL SELECT * from table WHERE endDay >= booking_ini_day AND endMonth >= booking_ini_month AND endDay <= booking_end_day AND endMonth <= booking_end_month -- get end period
4. Count days for each period/row in your result query and show to your user.

You will "duplicate" database information, like day, month and daymonth, but it's for better performance.

Answer 2

You can use MySQL date_format function when checking date.

For example:

select date_format('2016-05-18', '%m%d');
+-----------------------------------+
| date_format('2016-05-18', '%m%d') |
+-----------------------------------+
| 0518                              |
+-----------------------------------+
1 row in set (0,01 sec)

So, you can just check with simple interval: today is 0518 and its betwen Feb. 2nd and May 31st:

0202 <= 0518 <= 0531

Complete example:

desc mytable;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| date_from | char(4) | YES  |     | NULL    |       |
| date_to   | char(4) | YES  |     | NULL    |       |
| price     | int(11) | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
3 rows in set (0,00 sec)

select * from mytable;
+-----------+---------+-------+
| date_from | date_to | price |
+-----------+---------+-------+
| 0901      | 0930    |   100 |
| 1001      | 0201    |    80 |
| 0202      | 0531    |   100 |
| 0601      | 0701    |   120 |
+-----------+---------+-------+
4 rows in set (0,00 sec)

Sample query:

select price, date_from, date_to from mytable where date_format(now(), '%m%d') between date_from and date_to;
+-------+-----------+---------+
| price | date_from | date_to |
+-------+-----------+---------+
|   100 | 0202      | 0531    |
+-------+-----------+---------+
1 row in set (0,01 sec)

EDIT:

Read your comment on @FelipeDuarte's answer... For period from 10/01 (Oct. 1st) to 02/01 (Feb. 1st), we need to consider the new year...

You can cheat by splitting the period in two ones: 10/01 to 12/31 and 01/01 to 02/01