计算每两行时间戳差值：求解员工单次休息时长计算方法

Hey there! Let's get those break durations calculated for you. First, let's make your attendance records easier to read:

Original Attendance Records #

Your records are perfectly paired (each BREAK OUT has a matching BREAK IN right after), so we can use either SQL or Python to compute the time differences easily. Here are two common solutions:

Solution 1: Using SQL (Window Functions) #

If your data lives in a relational database, window functions are the way to go. We'll use LEAD() to grab the next timestamp for each employee, which will be their BREAK IN time after a BREAK OUT.

WITH paired_breaks AS (
    SELECT
        Name,
        Action,
        Datetime,
        -- Get the next timestamp for the same employee, ordered by time
        LEAD(Datetime) OVER (PARTITION BY Name ORDER BY Datetime) AS next_datetime
    FROM attendance
)
SELECT
    Name,
    Datetime AS break_start,
    next_datetime AS break_end,
    -- Calculate difference in minutes (swap to SECOND/HOUR if needed)
    TIMESTAMPDIFF(MINUTE, Datetime, next_datetime) AS break_duration_minutes
FROM paired_breaks
WHERE Action = 'BREAK OUT'; -- Only keep the start of each break

What this does: #

• PARTITION BY Name ensures we only pair breaks for the same employee.
• ORDER BY Datetime makes sure we grab the immediate next record (so the BREAK IN right after BREAK OUT).
• The final query filters to only BREAK OUT rows and computes the time difference between start and end.

Solution 2: Using Python (Pandas) #

If you're working with a CSV or local file, Pandas simplifies this process. Let's walk through the code:

import pandas as pd

# Load your data into a DataFrame
data = [
    [2, "John Doe", "BREAK OUT", "2018-05-24 09:00:41"],
    [3, "John Doe", "BREAK IN", "2018-05-24 09:10:45"],
    [4, "John Doe", "BREAK OUT", "2018-05-24 13:00:49"],
    [5, "John Doe", "BREAK IN", "2018-05-24 13:30:52"],
    [6, "John Doe", "BREAK OUT", "2018-05-24 15:30:56"],
    [7, "John Doe", "BREAK IN", "2018-05-24 15:40:59"]
]

df = pd.DataFrame(data, columns=["ID", "Name", "Action", "Datetime"])

# Convert the Datetime column to proper datetime type (critical for calculations)
df["Datetime"] = pd.to_datetime(df["Datetime"])

# Split into separate DataFrames for OUT and IN actions
breaks_out = df[df["Action"] == "BREAK OUT"].reset_index(drop=True)
breaks_in = df[df["Action"] == "BREAK IN"].reset_index(drop=True)

# Combine and calculate durations
break_durations = pd.DataFrame({
    "Name": breaks_out["Name"],
    "break_start": breaks_out["Datetime"],
    "break_end": breaks_in["Datetime"],
    "break_duration": breaks_in["Datetime"] - breaks_out["Datetime"]
})

# Print the result
print(break_durations)

Sample Output: #

Name         break_start           break_end break_duration
0  John Doe 2018-05-24 09:00:41 2018-05-24 09:10:45        00:10:04
1  John Doe 2018-05-24 13:00:49 2018-05-24 13:30:52        00:30:03
2  John Doe 2018-05-24 15:30:56 2018-05-24 15:40:59        00:10:03

Quick Notes: #

• This works because your records are ordered correctly. If you ever have unordered data, add df = df.sort_values(by=["Name", "Datetime"]) first.
• If you need durations in minutes instead of timedeltas, use:

  break_durations["break_duration_minutes"] = break_durations["break_duration"].dt.total_seconds() / 60
  

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