Somebody clocks in at 09:00, takes a break at 13:00, comes back at 13:45, and clocks out at 18:00. How many hours did they work?

Nine, minus forty-five minutes, is eight and a quarter. Almost every first attempt at this query returns nine, or eight and three quarters, or — most often — a number that is right for most people and wrong for the ones who took two breaks.

The schema that makes it tractable

Two tables, and the important decision is that a break is a row rather than a column:

CREATE TABLE time_entries (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     BIGINT UNSIGNED NOT NULL,
  started_at  DATETIME NOT NULL,          -- UTC
  ended_at    DATETIME NULL,
  INDEX (user_id, started_at)
);

CREATE TABLE breaks (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  time_entry_id  BIGINT UNSIGNED NOT NULL,
  started_at     DATETIME NOT NULL,
  ended_at       DATETIME NULL,
  INDEX (time_entry_id)
);

A break_minutes column on the entry looks simpler and cannot answer “when were they away”, which is the question that eventually gets asked.

The naive query, and why it double-counts

The obvious join produces a wrong answer the moment anybody takes two breaks:

-- WRONG
SELECT e.user_id,
       SUM(TIMESTAMPDIFF(SECOND, e.started_at, e.ended_at)
         - TIMESTAMPDIFF(SECOND, b.started_at, b.ended_at)) AS seconds
FROM time_entries e
LEFT JOIN breaks b ON b.time_entry_id = e.id
GROUP BY e.user_id;

With two breaks the join produces two rows for the entry, so the entry’s full duration is counted twice and only one break is subtracted from each. The total comes out roughly a full day too high, which is large enough to be noticed and subtle enough to be blamed on the tracker.

Aggregate the breaks first

Collapse breaks to one row per entry before joining:

SELECT e.user_id,
       SUM(
         TIMESTAMPDIFF(SECOND, e.started_at, e.ended_at)
         - COALESCE(b.break_seconds, 0)
       ) AS worked_seconds
FROM time_entries e
LEFT JOIN (
  SELECT time_entry_id,
         SUM(TIMESTAMPDIFF(SECOND, started_at, ended_at)) AS break_seconds
  FROM breaks
  WHERE ended_at IS NOT NULL
  GROUP BY time_entry_id
) b ON b.time_entry_id = e.id
WHERE e.ended_at IS NOT NULL
GROUP BY e.user_id;

The COALESCE is doing real work: without it, every entry with no breaks produces NULL and vanishes from the sum entirely, which gives you a total that is too low in exactly the opposite way.

The open entry nobody closed

WHERE ended_at IS NOT NULL is not a tidy-up; it is the difference between a report and a lie. Somebody who forgot to clock out on Friday has an entry with no end, and TIMESTAMPDIFF against NULL is NULL, which silently removes the row.

Decide explicitly what an open entry means in a report. Three defensible answers: exclude it and say how many were excluded, cap it at the expected working day, or close it automatically at a fixed time and mark how it ended.

-- cap an open entry at the organisation's day length
LEAST(
  TIMESTAMPDIFF(SECOND, e.started_at, COALESCE(e.ended_at, NOW())),
  o.expected_day_seconds
)

Breaks that run past the entry

A break that is still open when the entry closes, or that somehow ends after it, subtracts more than the entry contains and produces negative hours. It happens through clock skew, through a crash, and through a user clicking stop while on a break.

Clamp both ends rather than trusting the data:

SUM(
  TIMESTAMPDIFF(
    SECOND,
    GREATEST(b.started_at, e.started_at),
    LEAST(COALESCE(b.ended_at, e.ended_at), e.ended_at)
  )
)

Then assert the result cannot be negative, because the clamp is a repair and a repair should be visible:

GREATEST(0, ... )   -- and log when it fires

Overlapping entries

Two entries for the same person at the same time should be impossible and will exist. A desktop app and a browser tab both running, a device clock behind the server’s, a retry that created a second row.

Find them before trusting any total:

SELECT a.id, b.id, a.user_id, a.started_at, b.started_at
FROM time_entries a
JOIN time_entries b
  ON b.user_id = a.user_id
 AND b.id > a.id
 AND b.started_at < COALESCE(a.ended_at, NOW())
 AND a.started_at < COALESCE(b.ended_at, NOW());

A database constraint is better than a query, but MySQL has no exclusion constraints, so this is either an application-level check at write time or a scheduled job that reports them. Reporting them is the minimum; a total built on overlapping rows is wrong and nothing in the output says so.

Grouping by day, in the right zone

Everything above produces a total. The moment it is grouped by day, the day has to be a local day — covered properly in its own right, but the shape in this query is:

-- convert the bounds, not the column
WHERE e.started_at >= :day_start_utc
  AND e.started_at <  :day_end_utc

Converting the column with DATE(CONVERT_TZ(...)) is correct and cannot use the index on (user_id, started_at). On a table of any size that is the difference between a report that returns and one that times out.

Rounding, once, at the end

Keep seconds throughout and round once when displaying. Rounding each entry to the nearest quarter hour and then summing produces a total that does not match the sum of the displayed values, and somebody will add up the column by hand and find the discrepancy.

-- display only
ROUND(worked_seconds / 3600, 2) AS hours

If the business genuinely bills in rounded increments, do that rounding as a separate, named step on the invoice, not silently inside the hours query.

Checking it against a known day

The cheapest safety net is one fixture that a human has verified by hand:

public function test_two_breaks_do_not_double_count(): void
{
    $entry = TimeEntry::factory()->create([
        'started_at' => '2026-10-06 03:30:00',   // 09:00 IST
        'ended_at'   => '2026-10-06 12:30:00',   // 18:00 IST
    ]);
    Break_::factory()->for($entry)->create([
        'started_at' => '2026-10-06 07:30:00', 'ended_at' => '2026-10-06 08:15:00',
    ]);
    Break_::factory()->for($entry)->create([
        'started_at' => '2026-10-06 10:00:00', 'ended_at' => '2026-10-06 10:10:00',
    ]);

    $this->assertSame(8 * 3600 + 5 * 60, workedSeconds($entry->user_id));
}

Two breaks, because one break passes the broken query too.

A short answer

Breaks are rows, not a column. Aggregate them to one row per entry before joining, or the entry duration is counted once per break. COALESCE the result or entries without breaks disappear. Decide what an open entry means and say so. Clamp breaks to their entry and refuse negatives. Look for overlapping entries before trusting any total. Convert the bounds, not the column. Round once, at the end.