Reputation: 1134
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
Upvotes: 2
Views: 270
Reputation: 15131
I don't know your database tables, but I think you could do like this:
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
You will "duplicate" database information, like day, month and daymonth, but it's for better performance.
Upvotes: 2
Reputation: 1432
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
Upvotes: 2