MySQL同一查询中使用TIMEDIFF结果并减10小时的实现方法

Ah, I get it—this is a super common gotcha with MySQL! The problem is that you can’t reference a column alias (like hourDiff) in the same SELECT clause where you define it. MySQL processes the SELECT list in a way that the alias isn’t fully resolved yet when it tries to evaluate SUBTIME(hourDiff, ...). Let’s walk through a few straightforward fixes to get your query working.

Option 1: Repeat the TIMEDIFF Calculation #

The quickest fix is to replace the hourDiff alias with the actual TIMEDIFF(sluttid, starttid) expression directly in the SUBTIME function. Also, you had a tiny typo (SUBBTIME instead of SUBTIME)—let’s correct that too:

SELECT 
  id, 
  user_id, 
  starttid, 
  sluttid, 
  dato, 
  TIMEDIFF(sluttid, starttid) as hourDiff, 
  SUBTIME(TIMEDIFF(sluttid, starttid), '10:00:00') as overTime 
FROM hours 
WHERE user_id = :id 
ORDER BY dato;

This works because we’re using the raw calculation instead of relying on the alias, so MySQL doesn’t have to resolve a non-existent column name here.

Option 2: Use a Subquery (Derived Table) #

If you don’t want to repeat the calculation (especially useful if it’s more complex later on), wrap the initial calculation in a subquery first. This makes the hourDiff alias available in the outer query:

SELECT 
  id, 
  user_id, 
  starttid, 
  sluttid, 
  dato, 
  hourDiff, 
  SUBTIME(hourDiff, '10:00:00') as overTime 
FROM (
  SELECT 
    id, 
    user_id, 
    starttid, 
    sluttid, 
    dato, 
    TIMEDIFF(sluttid, starttid) as hourDiff 
  FROM hours 
  WHERE user_id = :id
) AS subquery 
ORDER BY dato;

Option 3: Use a CTE (MySQL 8.0+) #

If you’re running MySQL 8.0 or newer, a Common Table Expression (CTE) makes this even cleaner and easier to read:

WITH hour_calculations AS (
  SELECT 
    id, 
    user_id, 
    starttid, 
    sluttid, 
    dato, 
    TIMEDIFF(sluttid, starttid) as hourDiff 
  FROM hours 
  WHERE user_id = :id
)
SELECT 
  *, 
  SUBTIME(hourDiff, '10:00:00') as overTime 
FROM hour_calculations 
ORDER BY dato;

All three options will give you the overTime value you need by subtracting 10 hours from the time difference between sluttid and starttid.

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