SQL中计算行间日期时间差值：下一行减当前行（分钟单位）

Got it, let's walk through how to compute the minute difference between the next row's datetime and the current row's, then populate that value in the current row. I'll cover the most common tools people use for this task:

Excel / Google Sheets #

If you're working in a spreadsheet tool, this is straightforward:

• Assume your datetime values are in column A (with A1 as the header, data starting at A2)
• In cell B2 (the first row of your difference column), enter this formula:

  =IFERROR((A3-A2)*1440,"")
  

  • A3-A2 calculates the time difference between the next row and current row (Excel/Sheets stores time as fractions of a day)
  • Multiplying by 1440 converts that fraction to minutes (since 1 day = 24*60 = 1440 minutes)
  • IFERROR ensures the last row shows an empty string instead of an error (since there's no next row to compare)
• Drag the fill handle down to apply the formula to all rows

Python Pandas (For Data Analysis) #

If you're working with a dataset in Python, Pandas makes this easy with the shift() method:

import pandas as pd

# Load your data (replace with your actual data source)
df = pd.read_csv("your_data.csv")

# First, make sure your datetime column is parsed as a datetime type
df["datetime"] = pd.to_datetime(df["datetime"])

# Calculate the minute difference: next row datetime minus current row
df["minutes_diff"] = (df["datetime"].shift(-1) - df["datetime"]).dt.total_seconds() / 60

# Replace NaN (for the last row) with an empty string if needed
df["minutes_diff"] = df["minutes_diff"].fillna("")

# View or save the result
print(df.head())
# df.to_csv("result.csv", index=False)

• shift(-1) shifts the datetime column up by one row, so each row gets the next row's datetime value
• dt.total_seconds() converts the time difference to total seconds, then divide by 60 to get minutes

SQL (Database Queries) #

For database tables, use the LEAD() window function to get the next row's datetime:

MySQL / MariaDB #

SELECT
  datetime_col,
  TIMESTAMPDIFF(MINUTE, datetime_col, LEAD(datetime_col) OVER (ORDER BY datetime_col)) AS minutes_diff
FROM your_table;

PostgreSQL #

SELECT
  datetime_col,
  EXTRACT(EPOCH FROM (LEAD(datetime_col) OVER (ORDER BY datetime_col) - datetime_col)) / 60 AS minutes_diff
FROM your_table;

SQL Server #

SELECT
  datetime_col,
  DATEDIFF(MINUTE, datetime_col, LEAD(datetime_col) OVER (ORDER BY datetime_col)) AS minutes_diff
FROM your_table;

• LEAD(datetime_col) OVER (ORDER BY datetime_col) fetches the datetime value from the next row, ordered by the datetime column (adjust the ORDER BY clause if you need a different sort order, like by an ID column)
• The date difference function converts the time gap directly to minutes based on your database's syntax

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