如何将时间字符串转为小数时长？Excel与SQL批量实现方法

Alright, let's figure out how to convert those Chinese time strings like "2小时30分钟", "2小时", or "1小时15分钟" into decimal hours (like 2.5). I'll walk through step-by-step solutions for both Excel and SQL, including how to handle batch processing for each.

Excel Solution #

Formula Breakdown #

If you're using Excel 365 or 2021, this concise formula handles all three cases (hours + minutes, only hours, only minutes) seamlessly:

=SUM(IFERROR(--TEXTBEFORE(A1,{"小时","分钟"})*{1,1/60},0))

Here's what it does:

• TEXTBEFORE(A1,{"小时","分钟"}) extracts the numbers before "小时" and "分钟" (returns an array like {2,30} for "2小时30分钟")
• -- converts the extracted text to numbers
• *{1,1/60} multiplies the hour value by 1 and the minute value by 1/60 (to convert minutes to decimal hours)
• SUM adds the two values together, and IFERROR handles cases where either "小时" or "分钟" is missing (replaces missing values with 0)

For older Excel versions that don't support TEXTBEFORE, use this compatible formula:

=IFERROR(VALUE(LEFT(A1,SEARCH("小时",A1)-1)),0) + IFERROR(VALUE(MID(A1,SEARCH("分钟",A1)-LEN(MID(A1,1,SEARCH("分钟",A1)-1))+1,2))/60,0)

This uses SEARCH to find the position of "小时" and "分钟", then extracts the corresponding numbers to calculate the total decimal hours.

Batch Processing in Excel #

• Apply the formula to multiple rows: Enter the formula in the first cell (e.g., B1), then hover over the bottom-right corner of the cell until you see a crosshair. Double-click the crosshair to auto-fill the formula down the entire column, or drag it manually to cover all rows.
• Replace original data with results: Once you have the decimal values, select the entire column of results, copy it (Ctrl+C), then right-click the original time string column, choose Paste Special > Values to replace the text with the calculated decimal hours.

SQL Solution #

The approach varies slightly depending on your database system. Below are solutions for the most common platforms:

MySQL #

Use regular expressions to extract hour and minute values, then calculate the decimal total:

SELECT 
  time_str,
  (
    IFNULL(CAST(REGEXP_SUBSTR(time_str, '[0-9]+(?=小时)') AS DECIMAL(10,2)), 0) +
    IFNULL(CAST(REGEXP_SUBSTR(time_str, '[0-9]+(?=分钟)') AS DECIMAL(10,2))/60, 0)
  ) AS decimal_hours
FROM your_table;

• REGEXP_SUBSTR(time_str, '[0-9]+(?=小时)') matches and extracts the number immediately before "小时"
• IFNULL handles cases where "小时" or "分钟" isn't present (defaults to 0)
• Batch update: To save the decimal values directly to your table, run this UPDATE statement:

UPDATE your_table
SET decimal_hours_column = (
    IFNULL(CAST(REGEXP_SUBSTR(time_str, '[0-9]+(?=小时)') AS DECIMAL(10,2)), 0) +
    IFNULL(CAST(REGEXP_SUBSTR(time_str, '[0-9]+(?=分钟)') AS DECIMAL(10,2))/60, 0)
);

SQL Server #

SQL Server uses PATINDEX and SUBSTRING since its regex support is more limited:

SELECT 
  time_str,
  CASE
    WHEN CHARINDEX('小时', time_str) > 0 THEN
      CAST(SUBSTRING(time_str, 1, CHARINDEX('小时', time_str)-1) AS DECIMAL(10,2)) +
      ISNULL(CAST(SUBSTRING(time_str, CHARINDEX('小时', time_str)+2, CHARINDEX('分钟', time_str)-CHARINDEX('小时', time_str)-2) AS DECIMAL(10,2))/60, 0)
    WHEN CHARINDEX('分钟', time_str) > 0 THEN
      CAST(SUBSTRING(time_str, 1, CHARINDEX('分钟', time_str)-1) AS DECIMAL(10,2))/60
    ELSE 0
  END AS decimal_hours
FROM your_table;

• CHARINDEX finds the position of "小时" and "分钟" in the string
• The CASE statement handles all three possible time string formats
• Batch update: Use the same CASE logic in an UPDATE query to populate a decimal column:

UPDATE your_table
SET decimal_hours_column = CASE
    WHEN CHARINDEX('小时', time_str) > 0 THEN
      CAST(SUBSTRING(time_str, 1, CHARINDEX('小时', time_str)-1) AS DECIMAL(10,2)) +
      ISNULL(CAST(SUBSTRING(time_str, CHARINDEX('小时', time_str)+2, CHARINDEX('分钟', time_str)-CHARINDEX('小时', time_str)-2) AS DECIMAL(10,2))/60, 0)
    WHEN CHARINDEX('分钟', time_str) > 0 THEN
      CAST(SUBSTRING(time_str, 1, CHARINDEX('分钟', time_str)-1) AS DECIMAL(10,2))/60
    ELSE 0
  END;

PostgreSQL #

PostgreSQL's regex support makes this straightforward with SUBSTRING:

SELECT 
  time_str,
  (
    COALESCE((SUBSTRING(time_str FROM '(\d+)小时'))::DECIMAL(10,2), 0) +
    COALESCE((SUBSTRING(time_str FROM '(\d+)分钟'))::DECIMAL(10,2)/60, 0)
  ) AS decimal_hours
FROM your_table;

• SUBSTRING(time_str FROM '(\d+)小时') extracts the number before "小时" using a regex capture group
• COALESCE replaces NULL values (when a time component is missing) with 0
• Batch update: Save the results to your table with:

UPDATE your_table
SET decimal_hours_column = (
    COALESCE((SUBSTRING(time_str FROM '(\d+)小时'))::DECIMAL(10,2), 0) +
    COALESCE((SUBSTRING(time_str FROM '(\d+)分钟'))::DECIMAL(10,2)/60, 0)
);

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