在Microsoft SQL Server中如何基于日期时间拆分跨天停机记录行

Got it, let's tackle this machine downtime dashboard problem for your university project. The core challenge here is handling downtime that spans multiple days—you need to split those total hours into each corresponding date, then apply your daily availability formula: (24 - 当日停机时长) / 24. Here's a clear, actionable breakdown:

1. Core Logic for Splitting Cross-Day Downtime #

First, let's define the rules to split any downtime interval into daily chunks:

• First day: Calculate the duration from the downtime start time to the end of that day (23:59:59)
• Middle full days: For every complete day between the start and end date, assign a full 24 hours of downtime
• Last day: Calculate the duration from the start of that day (00:00:00) to the downtime end time

2. Practical Example #

Let's use a concrete downtime record to see this in action:

停机开始: 2024-05-20 18:00 | 停机结束: 2024-05-22 06:00

Split results:

• 2024-05-20: 停机时长 = 24 - 18 = 6 hours → 可用性 = (24-6)/24 = 0.75
• 2024-05-21: 停机时长 = 24 hours → 可用性 = (24-24)/24 = 0
• 2024-05-22: 停机时长 = 6 hours → 可用性 = (24-6)/24 = 0.75

3. Code Implementation (Python Example) #

Since university projects often use Python for data processing, here's a reusable function to split downtime records and calculate daily availability:

from datetime import datetime, timedelta

def split_and_calculate_availability(start_time: datetime, end_time: datetime):
    daily_results = []
    current_date = start_time.date()
    end_date = end_time.date()

    # Handle first day
    day_end = datetime.combine(current_date, datetime.max.time())
    first_day_hours = (day_end - start_time).total_seconds() / 3600
    first_day_availability = (24 - first_day_hours) / 24
    daily_results.append({
        "date": current_date,
        "downtime_hours": round(first_day_hours, 2),
        "availability": round(first_day_availability, 4)
    })

    # Handle middle full days
    current_date += timedelta(days=1)
    while current_date < end_date:
        daily_results.append({
            "date": current_date,
            "downtime_hours": 24.0,
            "availability": 0.0
        })
        current_date += timedelta(days=1)

    # Handle last day
    if current_date == end_date:
        day_start = datetime.combine(current_date, datetime.min.time())
        last_day_hours = (end_time - day_start).total_seconds() / 3600
        last_day_availability = (24 - last_day_hours) / 24
        daily_results.append({
            "date": current_date,
            "downtime_hours": round(last_day_hours, 2),
            "availability": round(last_day_availability, 4)
        })

    return daily_results

# Test the function with our example
start = datetime(2024, 5, 20, 18, 0)
end = datetime(2024, 5, 22, 6, 0)
dashboard_data = split_and_calculate_availability(start, end)

# Print formatted results
for entry in dashboard_data:
    print(f"Date: {entry['date']} | Downtime: {entry['downtime_hours']}h | Availability: {entry['availability']}")

4. Dashboard Display Tips #

To make your dashboard effective:

• Use a table widget to show daily date, downtime hours, and availability clearly
• Add a line chart to visualize availability trends over time
• Highlight days where availability drops below a threshold (e.g., 0.9) with color coding for quick alerts

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