Introduction to MySQL TIME Data Type

MySQL TIME stores a time-of-day value or an elapsed duration. Unlike DATETIME and TIMESTAMP, it has no date or time-zone conversion. A TIME value can be negative or longer than 24 hours.

Range and fractional seconds

The range is -838:59:59 through 838:59:59. MySQL supports fractional seconds precision from 0 to 6 digits; if omitted, the precision defaults to 0. Values with more fractional digits than the column precision are rounded on insertion.

Syntax

The TIME data type can be defined using the following syntax:

TIME[(fractional_seconds)]

Where fractional_seconds parameter specifies the number of decimal places for the seconds, ranging from 0 to 6, with a default value of 0.

Use Cases

The TIME data type is commonly used to store time durations or the time when an event occurs. For example, you can use the TIME data type to store an employee’s working hours, the execution time of a process, the duration of a game, etc.

Input values

Use an explicit colon-delimited value such as '08:30:00' for 8:30 AM or '27:15:00' for an elapsed duration of 27 hours and 15 minutes. MySQL also accepts a day-and-time form such as '2 03:15:00' for 2 days, 3 hours, and 15 minutes.

Avoid compact values when readability matters. For example, '1112' is interpreted as 11 minutes and 12 seconds ('00:11:12'), not 11 hours and 12 minutes. Use '11:12:00' for that time-of-day value.

Examples

Here are two examples of using the TIME data type:

Storing Employee’s Working Hours

Suppose we need to store the working hours of employees on a daily basis, we can use the TIME data type for storage. The specific table creation statement would be as follows:

CREATE TABLE employee (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  work_time TIME,
  PRIMARY KEY (id)
);

Suppose we want to insert an employee named “Tom” with a working time of 8 hours and 30 minutes, we can use the following INSERT statement:

INSERT INTO employee (name, work_time) VALUES ('Tom', '08:30:00');

Storing Game’s Duration

Suppose we need to store the duration of a game, we can use the TIME data type for storage. The specific table creation statement would be as follows:

CREATE TABLE game (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  start_time TIME,
  end_time TIME,
  PRIMARY KEY (id)
);

Suppose we want to insert a game named “Basketball Game” with a start time of 2:00 PM and an end time of 4:30 PM, we can use the following INSERT statement:

INSERT INTO game (name, start_time, end_time) VALUES ('Basketball Game', '14:00:00', '16:30:00');

Conclusion

TIME is a MySQL time-of-day and duration type, with a range wider than one day and fractional seconds precision up to six digits. For examples of duration arithmetic and formatting, see the MySQL TIME usage tutorial.