The TIME data type in MySQL is used to store and represent time values. It allows you to store time information without a date component. This data type is particularly useful when you need to store and manipulate time-based data, such as the duration of an event, the time of day, or the execution time of a specific task.

Here are some key points about the MySQL TIME data type:

Syntax

TIME[(fsp)]

The optional fsp (fractional seconds precision) parameter specifies the number of fractional seconds to store, ranging from 0 to 6.

Example

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    task_name VARCHAR(255),
    duration TIME
);

Storage Format

The TIME data type stores time values in the format ‘HH:MM:SS[.fraction]’, where HH represents hours (00 to 23), MM represents minutes (00 to 59), SS represents seconds (00 to 59), and ‘fraction’ represents fractional seconds if specified.

Inserting Values

INSERT INTO example_table (id, task_name, duration) 
VALUES
(1, 'Task A', '02:30:45'),
(2, 'Task B', '00:45:20.500');

Values can be inserted in the ‘HH:MM:SS’ format or with fractional seconds if specified.

Manipulating TIME Values

You can perform various operations on TIME values, such as addition, subtraction, and comparison.

SELECT 
duration + '01:15:00' AS updated_duration 
FROM example_table 
WHERE task_name = 'Task A';

This query adds 1 hour and 15 minutes to the duration of ‘Task A’.

Functions

CURTIME(): Returns the current time.

SELECT CURTIME();

EXTRACT(): Extracts components of a TIME value.

SELECT 
EXTRACT(HOUR FROM time_column) AS hours 
FROM example_table;

Format Specifiers

When retrieving TIME values in a specific format, you can use the TIME_FORMAT function or the FORMAT specifier with the DATE_FORMAT function.

Default Value

The default value for a TIME column is ’00:00:00′.

ALTER TABLE example_table 
MODIFY duration TIME DEFAULT '00:00:00';

In summary, the TIME data type in MySQL is designed for storing and manipulating time-based information. It allows for the representation of time values with or without fractional seconds and supports various operations and functions for working with time data in a database.