计算时间区间重叠天数 统计指定时段内服务业务占用天数

前置处理步骤 #

• 把数据中的开始时间、结束时间两个字段从字符串转为标准日期格式，避免格式不统一导致计算错误
• 过滤出所有type = service的记录，排除无关数据减少计算量

核心重叠天数计算逻辑 #

单条service记录和统计区间的重叠天数计算公式为：
max(0, min(记录结束时间, 统计区间结束时间) - max(记录开始时间, 统计区间开始时间) + 1)
公式中的+1是因为起止当天均计入统计，比如3月12日到3月12日计为1天，无重叠时结果为0。

不同工具实现方案 #

Excel/Google表格 #

• 先给4列依次命名为start_date、end_date、name、type，选中日期列设置单元格格式为日期，确保系统可以正常识别
• 统计指定区间总天数公式：=SUMPRODUCT(--(D:D="service"), --(B:B>=统计起始日), --(A:A<=统计结束日), (MIN(B:B, 统计结束日) - MAX(A:A, 统计起始日) + 1))，把公式中的统计起始日、结束日替换为对应日期即可，比如2021/1/5、2021/2/28
• 分月统计时，把统计起始日、结束日替换为对应月份的第一天和最后一天即可

Python（pandas） #

示例代码如下：

import pandas as pd
# 读取数据，这里假设你是csv，分隔符是|
df = pd.read_csv('你的数据文件路径', sep='|', names=['start_date', 'end_date', 'name', 'type'], skipinitialspace=True)
# 转日期格式，匹配你的mm-dd-yyyy格式
df['start_date'] = pd.to_datetime(df['start_date'].str.strip(), format='%m-%d-%Y')
df['end_date'] = pd.to_datetime(df['end_date'].str.strip(), format='%m-%d-%Y')
# 过滤service类，去掉字段前后空格
service_df = df[df['type'].str.strip() == 'service'].copy()

# 计算指定区间总天数
stat_start = pd.to_datetime('2021-01-05')
stat_end = pd.to_datetime('2021-02-28') # 2月无30日，替换为合法月末日期
service_df['overlap_days'] = (
    (service_df[['end_date', stat_end]].min(axis=1) - service_df[['start_date', stat_start]].max(axis=1)).dt.days + 1
).clip(lower=0)
total_days = service_df['overlap_days'].sum()
print(f"区间总service天数：{total_days}")

# 分月统计
# 把每条记录的时间范围拆为单日
service_df['date'] = service_df.apply(lambda x: pd.date_range(x['start_date'], x['end_date']), axis=1)
service_df = service_df.explode('date')
# 筛选统计区间内的日期
service_df = service_df[(service_df['date'] >= stat_start) & (service_df['date'] <= stat_end)]
# 按月份分组统计，nunique是单日多条记录只算1天，换成count就是多条算多天
monthly_days = service_df.groupby(service_df['date'].dt.month)['date'].nunique()
print("各月service天数：\n", monthly_days)

SQL #

假设表名为service_records，字段依次为start_date、end_date、name、type，且日期字段为datetime类型：

-- 统计指定区间总天数
SELECT SUM(
    DATEDIFF(
        LEAST(end_date, '2021-02-28'),
        GREATEST(start_date, '2021-01-05')
    ) + 1
) AS total_service_days
FROM service_records
WHERE type = 'service'
AND end_date >= '2021-01-05'
AND start_date <= '2021-02-28';

-- 分月统计（适配MySQL8.0+/PostgreSQL等支持递归CTE的数据库）
WITH RECURSIVE dates AS (
    SELECT MIN(start_date) AS dt FROM service_records
    UNION ALL
    SELECT dt + INTERVAL 1 DAY FROM dates WHERE dt < (SELECT MAX(end_date) FROM service_records)
)
SELECT MONTH(d.dt) AS month, COUNT(DISTINCT d.dt) AS service_days -- 不去重可删除DISTINCT
FROM dates d
JOIN service_records s
ON d.dt BETWEEN s.start_date AND s.end_date
WHERE s.type = 'service'
AND d.dt BETWEEN '2021-01-01' AND '2021-02-28'
GROUP BY MONTH(d.dt);

注意事项 #

• 注意日期合法性：2月没有30日，统计时要把非法日期替换为对应月份的最后一天
• 提前明确去重规则：如果同一天存在多条service记录，需要统计为1天还是多天，上述代码中均标注了对应修改位置
• 跨时区数据要先统一时区，再进行日期计算，避免出现日期偏移错误

内容的提问来源于stack exchange，提问作者Nils Grothmann