技术问询：基于时间戳及表结构计算累计有效工作时长

Alright, let's break this down into two clear, actionable parts—since you’ve got two related but distinct tasks here. First, let’s anchor ourselves on the foundation: your custom work rules table. I’ll assume this table defines valid work days (e.g., Mon-Fri), daily work start/end times, and excludes holidays. We’ll reference this table throughout both solutions.

1. Calculate Effective Work Duration Between Two Independent Timestamps #

This is the "single pair" scenario. The core idea is to iterate through each day in the timestamp range, check if it’s a valid work day, then compute the overlapping time between the timestamp range and the day’s work hours.

Step-by-Step Process: #

• First, normalize your timestamps: ensure they’re in the same timezone, and identify which is the start (earlier_ts) and end (later_ts).
• Iterate through each calendar day from earlier_ts.date() to later_ts.date().
• For each day:
  1. Check your custom rules table to confirm it’s a valid work day (not a weekend/holiday).
  2. Fetch the day’s work window (e.g., 9:00 AM to 5:30 PM).
  3. Calculate the actual overlapping window for that day:
     • actual_start = max(earlier_ts, work_window_start)
     • actual_end = min(later_ts, work_window_end)
  4. If actual_start < actual_end, add the duration (in seconds/hours) to your total.
• Sum all valid overlapping durations to get the total effective work time.

Example Pseudocode (Python): #

from datetime import datetime, timedelta

def calculate_single_work_duration(earlier_ts: datetime, later_ts: datetime, work_rules: dict) -> float:
    total_hours = 0.0
    current_date = earlier_ts.date()
    end_date = later_ts.date()

    while current_date <= end_date:
        # Look up rules for the current date (handle week-based rules + holiday overrides)
        day_rule = work_rules.get(current_date)
        if not day_rule:  # Skip non-work days/holidays
            current_date += timedelta(days=1)
            continue
        
        # Combine date with daily work times
        work_start = datetime.combine(current_date, day_rule["start_time"])
        work_end = datetime.combine(current_date, day_rule["end_time"])
        
        # Calculate overlapping period
        period_start = max(earlier_ts, work_start)
        period_end = min(later_ts, work_end)
        
        if period_start < period_end:
            duration_seconds = (period_end - period_start).total_seconds()
            total_hours += duration_seconds / 3600  # Convert to hours
        
        current_date += timedelta(days=1)
    
    return round(total_hours, 2)

2. Batch Calculate Cumulative Effective Work Duration for a Data Table #

For a table with start_time and end_time columns, you’ve got two main approaches: using SQL (if your data lives in a relational database) or scripting (if you’re processing the data in a tool like Python/R).

Approach 1: SQL (Relational Databases like PostgreSQL) #

The key here is to generate a "work calendar" from your custom rules, then compute overlaps between each table row’s time range and the calendar entries.

-- First, generate a calendar of all valid work periods from your custom rules
WITH work_calendar AS (
    SELECT
        -- Combine rule date with work start/end times
        (rule_date::timestamp + work_start_time) AS period_start,
        (rule_date::timestamp + work_end_time) AS period_end
    FROM custom_work_rules
    WHERE is_valid_work_day = TRUE  -- Adjust to match your table's flag
)

-- Calculate total work duration for each row in your data table
SELECT
    t.record_id,
    t.start_time,
    t.end_time,
    -- Sum overlapping durations (convert interval to hours for readability)
    SUM(
        GREATEST(0, 
            EXTRACT(EPOCH FROM LEAST(t.end_time, wc.period_end) - GREATEST(t.start_time, wc.period_start))
        ) / 3600
    ) AS total_effective_work_hours
FROM your_time_data_table t
-- Join only with work periods that overlap the row's time range
JOIN work_calendar wc
    ON wc.period_start < t.end_time
    AND wc.period_end > t.start_time
GROUP BY t.record_id, t.start_time, t.end_time
ORDER BY t.record_id;

Approach 2: Scripted Batch Processing #

If you’re pulling the data into a script, you can reuse the single-pair function from part 1. Just iterate over each row in your dataset, compute the duration for that row, and either store the result back or accumulate the total.

import pandas as pd
from datetime import datetime

# Assume you've loaded your data into a DataFrame
df = pd.read_csv("your_time_data.csv")
# Convert string timestamps to datetime objects (adjust format as needed)
df["start_time"] = pd.to_datetime(df["start_time"])
df["end_time"] = pd.to_datetime(df["end_time"])

# Load your custom work rules (e.g., from a CSV or database query)
work_rules = load_work_rules()  # Implement this based on your data source

# Calculate duration for each row
df["effective_work_hours"] = df.apply(
    lambda row: calculate_single_work_duration(row["start_time"], row["end_time"], work_rules),
    axis=1
)

# Get cumulative total across all rows
cumulative_total = df["effective_work_hours"].sum()
print(f"Cumulative effective work duration: {cumulative_total:.2f} hours")

Critical Notes to Avoid Mistakes: #

• Timezone Consistency: Ensure all timestamps (in your data table and work rules) use the same timezone—mixing UTC and local time will wreck your calculations.
• Holiday Overrides: Your custom rules table should handle exceptions (e.g., a holiday falling on a regular workday) explicitly.
• Partial Days: Don’t forget to handle cases where the start/end timestamps fall in the middle of a workday (the max()/min() logic takes care of this).

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