MySQL TIMEDIFF Less than an hour, invalid time

I have a function in MySQL that will select the time someone clocked in, and select the time someone clocked out and link them together. It will calculate how many hours that person has been on the clock in total between two provided dates. I then created another function that selects the break events and subtracts the break time from the total hours worked. It works great on any break times that are greater than an hour, anything less than that and it returns invalid data.

SELECT A1.volunteer_id, A1.datetime AS check_in_at, A2.datetime AS check_out_at,
       TIME_FORMAT(TIMEDIFF(A2.datetime, A1.datetime), '%h.%i') AS total_time
FROM volunteer_timeclock AS A1 INNER JOIN 
     volunteer_timeclock AS A2
     ON A1.volunteer_id = A2.volunteer_id AND 
        DATE(A1.datetime) = DATE(A2.datetime)
WHERE A1.status = 'break_start' AND
      A2.status = 'break_end' AND 
      DATE(A1.datetime) BETWEEN '2020-01-01' AND '2020-12-31' AND
      DATE(A2.datetime) BETWEEN '2020-01-01' AND '2020-12-31'
ORDER BY A1.datetime DESC

This query will grab all of the break time between the beginning and the end of the year. The record I have in the database is 8:00AM to 9:15AM was a 15 minute break, and this will return 12.15 instead of 0.15.

Here is the data I currently have created inside of the database:

Here is the data I get back from my queries:

As you can see the yearly calculation is off because it is subtracting the break time of 12.15

Thank you!

Answer

In summary

Try using %H instead of %h – it probably does something closer to what you’re expecting.

The long version

Look closely at the documentation for DATE_FORMAT and TIME_FORMAT.

For DATE_FORMAT, the specifier %h formats as: Hour (01..12). It also notes:

The specifiers apply to other functions as well: STR_TO_DATE(), TIME_FORMAT(), UNIX_TIMESTAMP().

For TIME_FORMAT:

TIME_FORMAT(time,format)

This is used like the DATE_FORMAT() function, but the format string may contain format specifiers only for hours, minutes, seconds, and microseconds. Other specifiers produce a NULL value or 0.

If the time value contains an hour part that is greater than 23, the %H and %k hour format specifiers produce a value larger than the usual range of 0..23. The other hour format specifiers produce the hour value modulo 12.

So, since 00:15:00 has an hour value of zero – not greater than 23 – the modulo 12 exception does not apply; TIME_FORMAT behaves like DATE_FORMAT, and the hour 00 is formatted as 12.

    TIME_FORMAT('00:15:00', '%H %k %h %I %l')
1   00 0 12 12 12