MySQL中如何最优计算incode/outcode对应的时长？

Great question! Your current approach works for simple scenarios, but it has a critical flaw: it doesn’t handle cases where the outcode’s frame count is lower than the incode’s (e.g., incode = '10:00:01:20', outcode = '10:01:05:02'). In this case, your code would return a negative frame value (-18) instead of correctly borrowing 1 second (25 frames) to get 07.

A more robust and cleaner approach is to convert the entire timestamp to total frames first, calculate the difference, then convert back to the HH:MM:SS:FF format. This method automatically handles frame borrows and reduces repeated string manipulation.

Step-by-Step Explanation #

1. Convert timestamps to total frames: Since your framerate is 25fps, each unit translates to:
   • 1 hour = 3600 * 25 frames
   • 1 minute = 60 * 25 frames
   • 1 second = 25 frames
   • Frames = raw frame value
2. Calculate the frame difference: Subtract the incode's total frames from the outcode's.
3. Convert frame difference back to HH:MM:SS:FF: Split the total frame difference into hours, minutes, seconds, and remaining frames, then pad each segment to 2 digits.

Optimized Code (Single Query) #

SELECT 
  CONCAT(
    LPAD(FLOOR(diff_frames / (3600 * 25)), 2, '0'), ':',
    LPAD(FLOOR((diff_frames % (3600 * 25)) / (60 * 25)), 2, '0'), ':',
    LPAD(FLOOR((diff_frames % (60 * 25)) / 25), 2, '0'), ':',
    LPAD(diff_frames % 25, 2, '0')
  ) AS Duration
FROM (
  SELECT 
    -- Calculate total frames for outcode
    (SUBSTRING_INDEX(outcode, ':', 1) * 3600 * 25) +
    (SUBSTRING_INDEX(SUBSTRING_INDEX(outcode, ':', 2), ':', -1) * 60 * 25) +
    (SUBSTRING_INDEX(SUBSTRING_INDEX(outcode, ':', 3), ':', -1) * 25) +
    SUBSTRING_INDEX(outcode, ':', -1)
    -- Minus total frames for incode
    - (
      SUBSTRING_INDEX(incode, ':', 1) * 3600 * 25 +
      SUBSTRING_INDEX(SUBSTRING_INDEX(incode, ':', 2), ':', -1) * 60 * 25 +
      SUBSTRING_INDEX(SUBSTRING_INDEX(incode, ':', 3), ':', -1) * 25 +
      SUBSTRING_INDEX(incode, ':', -1)
    ) AS diff_frames
  FROM (
    -- Replace with your actual table/values
    SELECT '10:00:01:00' AS incode, '10:01:05:02' AS outcode
  ) AS timestamps
) AS frame_difference;

Simplified Version (For Edge-Case-Free Scenarios) #

If you’re certain the outcode’s frame count will always be greater than or equal to the incode’s, you can simplify your original code by using RIGHT() instead of SUBSTR() for cleaner frame extraction:

SELECT 
  CONCAT(
    SUBSTR(TIMEDIFF(outcode, incode), 1, 8), ':',
    LPAD(RIGHT(outcode, 2) - RIGHT(incode, 2), 2, '0')
  ) AS Duration
FROM (
  SELECT '10:00:01:00' AS incode, '10:01:05:02' AS outcode
) AS timestamps;

Why the Frame-Based Method is Better #

• Handles all edge cases: Automatically adjusts for frame borrows (e.g., 2 frames minus 20 frames becomes 7 frames with 1 second deducted).
• Scalable: If your framerate changes (e.g., 30fps), you only need to replace the 25 value everywhere.
• Cleaner logic: Reduces repetitive string operations and centralizes the calculation around a single unit (frames).

内容的提问来源于stack exchange，提问作者Mohamad Sami El Achi