mysql date within season

[dennisfreud]

Hi,

I am looking for general help here. I dont know how to start at this... maybe someone can point me in the right direction.

I need to calculate how many nights of a given time frame (variable dates) lie within certain ranges of times given to calcutate pricing for a hotel which has different prices depending on the season. (fixed dates).

So I have this table (MYSQL) which for example says:

from : to : price/night
01/01 : 03/31 : 100,-
04/01 : 12/20 : 120,-
12/21 : 12/31 : 160,-

How do I tackle (or at least attack) the problem to calculate the price of a stay in this hotel for, lets say Dec. 18th to Jan. 5th???

Obviously the dates which fix the pricing do not have/need the attribute (year) as they are always the same...

php boards have given me this advice
->
you could use the MySQL functions "DAY", "MONTH" to calculate whether that day of the month is in between two dates. You'd just have to give the date fields in the table a year (can be arbitrary, but pick a leap-year just-in-case; say 2000).
->

but I do not know how to put this into praxis...
Any help greatly appreciated.

THX
Dennis
(from sunny Tenerife island)

[amitpatel66]

Try This:

SELECT SUM(price) FROM hotel_info WHERE DATE_FORMAT(from, '%m/%d') >= '12/18' AND DATE_FORMAT(to,'%m/%d') <= '01/05'
 

[dennisfreud]

Thanks,

this is certainly going in the right direction.
Now I played around a little, simplified the time frame and with

SELECT price
FROM test
WHERE DATE_FORMAT( FROM, '%m/%d' ) >= '02/02'
AND DATE_FORMAT( TO, '%m/%d' ) <= '02/05'
 

returns only one row which is correct, (there is only one row for the whole season which has one price...) and I figure thats why SUM is not doing anything here.

So now I still need to find out how many days are within one season and how many seasons are affected by my initial travel dates...

Any more help welcome
Looks as if this was not trivial.

Dennis

[ronverdonk]

Pity you don't have the year because that complicates it. How about handling leap years, ie. feb having 29 days. And the number of days from dec 15 to jan 25? The latter wil always cost extra calculations because you have to 'invent' the year for functions like DIFFDATE() etc.

Ronald

[dennisfreud]

Hi,

thanks for all thoughts on this, greatly appreciated.

Well, of course I have the year of the client booking.
But when I get the rates information from the hotel they give them to me without a year as they are repeated every year...

So what good would it do if i store prices for 2008 and get inquiries for 2009?

Dennis

[ronverdonk]

That was not my remark. I asked about leap year handling and 'over-the-year' bookings.

Ronald