Menu

MySQL TIME: Time of Day and Elapsed Durations

Learn how MySQL TIME stores times and durations, why values can exceed 24 hours, how to handle literals and fractional seconds, and when to use DATETIME instead.

MySQL TIME can represent a time of day, an elapsed duration, or the difference between two events. Unlike DATETIME or TIMESTAMP, it does not include a date. It is also not converted between time zones.

Range and fractional seconds

TIME ranges from -838:59:59 to 838:59:59, so it can hold negative durations and values longer than one day. You can specify fractional seconds precision from 0 to 6 digits; the default is 0.

CREATE TABLE task_runs (
    run_id INT PRIMARY KEY,
    started_at DATETIME NOT NULL,
    elapsed TIME(3) NOT NULL
);

INSERT INTO task_runs (run_id, started_at, elapsed)
VALUES (1, '2026-09-24 08:00:00', '27:15:30.125');

Here elapsed records 27 hours, 15 minutes, and 30.125 seconds. If a value has more fractional digits than the column precision, MySQL rounds it when storing.

Enter time and duration values

Use a colon-delimited literal for clarity:

SELECT
    '11:12:00' AS time_of_day,
    '27:15:00' AS elapsed_duration,
    '2 03:15:00' AS days_and_time;

MySQL also accepts compact literals, but they can be easy to misread. For example, '1112' means 11 minutes and 12 seconds ('00:11:12'), not 11:12 AM. Prefer '11:12:00' when you mean 11 hours and 12 minutes.

Calculate durations

Use TIMEDIFF() to subtract two time values:

SELECT TIMEDIFF('17:30:00', '08:15:00') AS shift_length;

The result is 09:15:00. ADDTIME() and SUBTIME() can add or subtract durations, including values longer than 24 hours or negative results:

SELECT
    ADDTIME('27:15:00', '01:30:00') AS longer_duration,
    SUBTIME('02:00:00', '03:15:00') AS negative_duration;

For a time-of-day display, TIME_FORMAT() can format the value without changing the stored data:

SELECT TIME_FORMAT('15:30:00', '%h:%i %p') AS formatted_time;

This returns 03:30 PM. Use %H for a 24-hour hour (00–23); %h is the 12-hour form (01–12).

Choose TIME, DATETIME, or TIMESTAMP

  • Use TIME for a clock time or elapsed duration when the date is not needed. For a TIME value, '25:00:00' is valid and means a 25-hour duration, not 1:00 AM on the next day.
  • Use DATETIME when the date and wall-clock time belong together and MySQL should not convert the stored value through the session time zone.
  • Use TIMESTAMP when you need a timestamp that MySQL converts between the session time zone and UTC. It has a more limited date range.

Out-of-range but otherwise valid TIME values can be clipped to the nearest endpoint when restrictive SQL mode is disabled; invalid values can become 00:00:00. Check @@SESSION.sql_mode when input validation matters. See MySQL’s TIME type documentation and date/time literal formats for details.