The MySQL TIME function is a built-in function that is used to extract the time portion from a datetime or timestamp expression. It is particularly useful when you have a datetime value and you want to isolate and work with only the time component.
Syntax
The basic syntax of the TIME function is as follows:
TIME(expr)
Here, expr represents the datetime or timestamp expression from which you want to extract the time. The result is a time value.
Example
Let’s look at an example:
SELECT TIME('2024-01-01 14:30:45') AS extracted_time;
In this example, the TIME function extracts the time portion from the given datetime ‘2024-01-01 14:30:45′. The result will be ’14:30:45’.
You can also use the TIME function with column values. Consider the following example:
CREATE TABLE my_table (
id INT PRIMARY KEY,
full_datetime DATETIME
);
INSERT INTO my_table (id, full_datetime) VALUES
(1, '2024-01-01 08:45:00'),
(2, '2024-01-01 15:20:30'),
(3, '2024-01-01 21:10:15');
SELECT id, full_datetime,
TIME(full_datetime) AS extracted_time
FROM my_table;
In this case, the TIME function is applied to the full_datetime column, and it extracts the time component for each row. The result will include the original id, full_datetime, and the extracted time.
Keep in mind that the TIME function does not modify the original datetime or timestamp value; it simply returns the time part as a separate value. If you want to update the original value, you would need to use an UPDATE statement.
It’s worth noting that the TIME function is just one of many date and time functions provided by MySQL to manipulate and extract information from date and time values. Understanding these functions can be crucial for tasks involving time-based data analysis and reporting.