使用MySQL和PHP计算日期期间的被占用天数 [英] Calculate Number of Occupied Days within a date period using MySQL and PHP

问题描述

我有一个公寓预订系统，需要根据物业基础在一个财产的日期内生成偶然天数和未占用日数的报告。

在所选择的期限内考虑到某些预订可能在开始/结束日期之前和/或结束日期之后开始。

我发现这一点 - MySQL DateRange内的一个月内的天数（预订表） - 这是正确的，但我不希望它固定的，逐个月，而是在两个可变日期之间。

理想情况下，我只想使用MySQL，但是如果需要PHP，那就行了。

我可以想到的唯一办法就是循环每天单独检查当天是否有占用，但这似乎是不可思议的低效率。

编辑：
我设法适应其他问题的代码如下：

 创建表如果不存在`view_bookings`（ 
`bkg_id` int（11）NOT NULL AUTO_INCREMENT，
`apt_id` int（10）NOT NULL，
`apt_name` varchar（50）NOT NULL，
`start_date`日期DEFAULT NULL，
`end_date`日期DEFAULT NULL，
 PRIMARY KEY（`bkg_id`），
 UNIQUE KEY`bkg_id_UNIQUE`（`bkg_id`）
）ENGINE = InnoDB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1; 
 
 INSERT INTO`view_bookings`（'apt_id`，`apt_name`，`start_date`，`end_date`）VALUES 
（1，'Apartment One'，'2012-09-02' ，'2013-02-05'），
（1，'Apartment One'，'2013-02-05'，'2013-07-05'），
（2，'Apartment Two' ，'2012-12-25'，'2013-02-28'），
（2，'Apartment Two'，'2013-03-01'，'2013-04-10'），
（2，'Apartment Two'，'2013-04-16'，'2013-09-19'），
（3，'Apartment Three'，'2013-01-01'，'2013-02 -04'），
（3，'Apartment Three'，'2013-02-06'，'2013-02-12'），
（3，'Apartment Three'，'2013-02 -16'，'2013-02-27'），
（3，'Apartment Three'，'2013-02-27'，'2013-03-14'），
（3，'公寓三'，'2013-03-19'，'2013-06-12'）; 
 
 SELECT 
 SUM（
 1 + DATEDIFF（
 LEAST（end_date，'2013-03-30'），
 GREATEST（start_date，'2013 -02-01'）
）
）AS天，
 apt_name，
 apt_id 
 FROM 
 view_bookings 
 WHERE 
 start_date < ='2013-03-30'
 AND'2013-02-01'< = end_date 
 GROUP BY 
 apt_id 
  pre> 
 
 
但是，如果重叠预订，则可以计算两天。如何防止这种情况？
解决方案
可能通过生成开始和结束日期之间的所有日期，使用distinct来删除重复日期。做一个计数，以获得相关范围内唯一预订日期的总数。然后使用该数字与日期范围内的天数获得免费天数。
 
 
 
为每个公寓完成： -  
  SELECT apt_id，apt_name，DaysBooked AS DaysOccupied，DayNumber  -  DaysBooked AS DaysUnoccupied 
 FROM 
（
 SELECT apt_id，apt_name，COUNT （*）AS DaysBooked 
 FROM 
（
 SELECT DISTINCT view_bookings.apt_id，view_bookings.apt_name，DATE_ADD（view_bookings.start_date，INTERVAL units.i + tens.i * 10 + hundreds.i * 100天）AS BookedDate 
 FROM view_bookings 
 CROSS JOIN（SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9）单位
 CROSS JOIN（SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9）tens 
 CROSS JOIN（SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9）数百
 WHERE DATE_ADD（view_bookings.start_date，INTERVAL units.i + tens.i * 10 + hundreds.i * 100天）< = view_bookings.end_date 
 AND DATE_ADD（view_bookings.start_date，INTERVAL units.i + tens.i * 10 + hundreds.i * 100 DAY）BETWEEN'2013-02-01'AND'2013- 02-28'
）Sub1 
 GROUP BY apt_id，apt_name 
）Sub3 
 CROSS JOIN 
（
选择ABS（DATEDIFF（'2013-02- 01'，'2013-02-28'））+ 1 AS DayNumber  - 请注意，DATEDIFF给出差异天数，但您希望该数字包含开始和结束日期，因此添加1. 
）Sub2 
  
请注意，这只适用于最多1000天的日期范围，但容易扩展到更多。 
 
I have an apartment booking system and need to generate a report of number of occcupied days vs. unoccupied days within a date period on a property by property basis.

Bearing in mind within the chosen period that some bookings may start before and/or end after the start / end date respectivley.

I found this - MySQL Number of Days inside a DateRange, inside a month (Booking Table) - which is along the right lines, but I don't want it on a fixed, month by month basis but rather between two variable dates.

Ideally I'd like to do this using MySQL only but if PHP is needed then that is fine.

The only way I could think to do it would be to loop through each day individually and check if there was an occupancy on that day, but that just seems incredible inefficient.

Edit:
I managed to adapt the code from the other questions as follows:
CREATE TABLE IF NOT EXISTS `view_bookings` (
`bkg_id` int(11) NOT NULL AUTO_INCREMENT,
`apt_id` int(10) NOT NULL,
`apt_name` varchar(50) NOT NULL,
`start_date` date DEFAULT NULL,
`end_date` date DEFAULT NULL,
PRIMARY KEY (`bkg_id`),
UNIQUE KEY `bkg_id_UNIQUE` (`bkg_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `view_bookings` (`apt_id`, `apt_name`, `start_date`, `end_date`) VALUES
(1, 'Apartment One', '2012-09-02', '2013-02-05'),
(1, 'Apartment One', '2013-02-05', '2013-07-05'),
(2, 'Apartment Two', '2012-12-25', '2013-02-28'),
(2, 'Apartment Two', '2013-03-01', '2013-04-10'),
(2, 'Apartment Two', '2013-04-16', '2013-09-19'),
(3, 'Apartment Three', '2013-01-01', '2013-02-04'),
(3, 'Apartment Three', '2013-02-06', '2013-02-12'),
(3, 'Apartment Three', '2013-02-16', '2013-02-27'),
(3, 'Apartment Three', '2013-02-27', '2013-03-14'),
(3, 'Apartment Three', '2013-03-19', '2013-06-12');

SELECT
SUM(
    1 + DATEDIFF(
        LEAST(end_date, '2013-03-30'),
        GREATEST(start_date, '2013-02-01')
        )
    ) AS days,
    apt_name,
    apt_id
FROM
    view_bookings
WHERE
    start_date <= '2013-03-30'
AND '2013-02-01' <= end_date
GROUP BY
    apt_id
This works, however if there are overlapping bookings then it counts days twice. How can I prevent this?
解决方案
Possibly do it by generating all the dates between the start and end dates, using distinct to remove the duplicate dates. Do a count of that to get the total number of unique booked dates in the relevant range. Then use that figure with the count of days within the date range to get the number of free days.

Done for each apartment:-
SELECT apt_id, apt_name, DaysBooked AS DaysOccupied, DayNumber - DaysBooked AS DaysUnoccupied
FROM
(
    SELECT apt_id, apt_name, COUNT(*) AS DaysBooked
    FROM
    (
        SELECT DISTINCT view_bookings.apt_id, view_bookings.apt_name, DATE_ADD(view_bookings.start_date, INTERVAL units.i + tens.i * 10 + hundreds.i * 100 DAY) AS BookedDate
        FROM view_bookings
        CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
        CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
        CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) hundreds
        WHERE DATE_ADD(view_bookings.start_date, INTERVAL units.i + tens.i * 10 + hundreds.i * 100 DAY) <= view_bookings.end_date
        AND DATE_ADD(view_bookings.start_date, INTERVAL units.i + tens.i * 10 + hundreds.i * 100 DAY) BETWEEN '2013-02-01' AND '2013-02-28'
    ) Sub1
    GROUP BY apt_id, apt_name
) Sub3
CROSS JOIN 
(
    SELECT ABS(DATEDIFF('2013-02-01', '2013-02-28')) + 1 AS DayNumber -- Note that DATEDIFF is giving the difference in days but you want the figure to include the start and end dates so add 1.
) Sub2
Note that this in only coping with date ranges of up to 1000 days, but easily expanded to cover more.

                        
这篇关于使用MySQL和PHP计算日期期间的被占用天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！