MySQL TIME: Time of Day and Elapsed Durations

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.