Introduction to MySQL TIME Data Type

The TIME data type in MySQL is used to store time values, including hours, minutes, seconds, and milliseconds. It is commonly used to store time durations or the time when an event occurs.

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.

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

The TIME data type is a data type used to store time values, including hours, minutes, seconds, and milliseconds. It is commonly used to store time durations or the time when an event occurs.