拆分datetime列：按日统计机器运行时长及日历表适配问题

核心结论 #

不需要修改你的日历表——仅靠现有的date字段就能实现按日拆分时间段并统计运行时长。

实现思路（SQL场景） #

通过将日历表的date转换为当日的起止时间戳，再和你的运行时间段做关联，截取每个日期内的有效运行区间，最后计算时长。

示例SQL代码 #

假设你的运行数据存在machine_runs表（含machine_id、PreviousUp、Downstart字段），日历表为calendar（仅含cal_date字段）：

WITH split_runs AS (
    SELECT
        mr.machine_id,
        -- 取运行开始时间和当日0点的最大值，作为当日实际运行起始
        GREATEST(mr.PreviousUp, c.cal_date::TIMESTAMP) AS run_start,
        -- 取运行结束时间和当日23:59:59的最小值，作为当日实际运行结束
        LEAST(mr.Downstart, (c.cal_date + INTERVAL '1 day') - INTERVAL '1 second') AS run_end,
        c.cal_date
    FROM machine_runs mr
    JOIN calendar c
        -- 只关联和运行时间段有重叠的日期
        ON mr.PreviousUp < (c.cal_date + INTERVAL '1 day')
        AND mr.Downstart > c.cal_date::TIMESTAMP
)
SELECT
    machine_id,
    cal_date,
    -- 计算运行时长（分钟）
    ROUND(EXTRACT(EPOCH FROM (run_end - run_start)) / 60, 2) AS run_minutes,
    -- 计算运行时长（小时）
    ROUND(EXTRACT(EPOCH FROM (run_end - run_start)) / 3600, 2) AS run_hours
FROM split_runs
WHERE run_start <= run_end
ORDER BY machine_id, cal_date;

关键逻辑说明 #

1. 日期转时间戳：将cal_date转为TIMESTAMP得到当日00:00:00，cal_date + 1天 - 1秒得到当日23:59:59；
2. 区间截取：用GREATEST和LEAST截取每个日期内的有效运行片段，处理跨天的时间段；
3. 关联过滤：通过JOIN条件确保只处理和当前日期有重叠的运行记录，避免无效计算。

实现思路（Python Pandas场景） #

如果用Python处理，同样无需修改日历表，通过日期转换和区间筛选实现：

示例代码 #

import pandas as pd

# 加载数据（假设已读入）
# machine_runs = pd.read_csv("你的运行数据文件.csv")
# calendar = pd.read_csv("你的日历表文件.csv")

# 转换为datetime格式
machine_runs['PreviousUp'] = pd.to_datetime(machine_runs['PreviousUp'])
machine_runs['Downstart'] = pd.to_datetime(machine_runs['Downstart'])
calendar['cal_date'] = pd.to_datetime(calendar['date']).dt.normalize()

# 生成当日起止时间
calendar['day_start'] = calendar['cal_date']
calendar['day_end'] = calendar['cal_date'] + pd.Timedelta(days=1) - pd.Timedelta(seconds=1)

# 交叉连接运行记录和日历日期，筛选有效重叠区间
cross_join = machine_runs.merge(calendar, how='cross')
valid_runs = cross_join[
    (cross_join['PreviousUp'] < cross_join['day_end']) &
    (cross_join['Downstart'] > cross_join['day_start'])
]

# 计算当日有效运行时长
valid_runs['run_start'] = valid_runs[['PreviousUp', 'day_start']].max(axis=1)
valid_runs['run_end'] = valid_runs[['Downstart', 'day_end']].min(axis=1)
valid_runs['run_minutes'] = (valid_runs['run_end'] - valid_runs['run_start']).dt.total_seconds() / 60
valid_runs['run_hours'] = valid_runs['run_minutes'] / 60

# 整理输出结果
result = valid_runs[['machine_id', 'cal_date', 'run_minutes', 'run_hours']].round(2)
result = result.sort_values(['machine_id', 'cal_date'])
print(result)

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