The TIME()
function in MySQL is used to extract the time portion from a date or datetime expression, returning the time in the format 'HH:MM'.
This function is particularly useful when working with time components in databases, such as scheduling or logging systems. In this article, We will learn about the TIME() function in MYSQL along with their syntax and so on.
TIME() Function in MySQL
- MySQL TIME() function is used to extract the time portion from a date or datetime expression.
- If the expression is not a time or a datetime value, the TIME function will return '00:00:00'.
- If an expression is NULL, the TIME function will return NULL.
- The TIME() function is particularly useful when you need to isolate and work with the time component of a datetime value.
Syntax
TIME(expression)
Parameter:
This method accepts one parameter.
- expression: The time or datetime value from which we want to extract the time.
Return Value:
- It returns the time in the format 'HH:MM:SS'.
Examples of TIME() function in MySQL
Let us take a look at some of the examples of TIME() function in MYSQL:
Example 1: Basic Usage of TIME() Function
Extracting the time using TIME Function from a datetime expression where the datetime is specified in the format of YYYY-MM-DD HH-MM-SS.
SELECT TIME("2019-01-10 08:14:21");
Output:
Example 2: Using TIME() Function in the format of HH-MM-SS
Extracting the time using TIME Function from a datetime expression where the datetime is specified in the format of HH-MM-SS.
SELECT TIME("18:24:23") AS Time;
Output:
Example 3:Using TIME() Function for expression which is not a datetime
In this example, we are extracting the time using TIME Function from a expression which is not a datetime.
SELECT TIME(NULL) AS TIME;
Output:
Example 4: Using TIME() function on table column example
The TIME function can be used to set value of columns. To demonstrate create a table named "DeliveryDetails".
CREATE TABLE DeliveryDetails (
DeliveryId INT AUTO_INCREMENT,
ProductId INT NOT NULL,
ProductName VARCHAR(20) NOT NULL,
Delivered_At TIME NOT NULL,
PRIMARY KEY(DeliveryId)
)
Here, we will use TIME function when a delivery will be completed. The value in Delivered_At column will be the value given by TIME Function.
INSERT INTO
DeliveryDetails(ProductId, ProductName, Delivered_At)
VALUES
(12345, 'Let Us Java', TIME (NOW()));
Now, checking the DeliveryDetails table :
SELECT * FROM DeliveryDetails;
Output:
DELIVERYID | PRODUCTID | PRODUCTNAME | DELIVERED_AT |
---|
1 | 12345 | Let Us Java | 09:48:34 |
Example 5: Using TIME() in WHERE Clause
You can also use the TIME() function in a WHERE clause to filter records based on the time part of a datetime value.
To demonstrate create a table named "events".
MySQL
CREATE TABLE events (
event_id INT PRIMARY KEY,
event_name VARCHAR(50),
event_datetime DATETIME
);
INSERT INTO events (event_id, event_name, event_datetime) VALUES
(1, 'Meeting', '2024-06-26 15:45:30'),
(2, 'Conference', '2024-06-26 10:30:00'),
(3, 'Workshop', '2024-06-26 15:45:30'),
(4, 'Webinar', '2024-06-26 12:00:00');
event_id | event_name | event_datetime |
---|
1 | Meeting | 2024-06-26 15:45:30 |
2 | Conference | 2024-06-26 10:30:00 |
3 | Workshop | 2024-06-26 15:45:30 |
4 | Webinar | 2024-06-26 12:00:00 |
When you run the query:
SELECT *
FROM events
WHERE TIME(event_datetime) = '15:45:30';
Output:
event_id | event_name | event_datetime |
---|
1 | Meeting | 2024-06-26 15:45:30 |
3 | Workshop | 2024-06-26 15:45:30 |
Important Points about TIME() Function in MySQL
Here are the important points about the TIME() function in MySQL:
- The
TIME()
function extracts the time part from a given date-time expression, returning the time in a hh:mm:ss[.nnnnnnn]
format. - It can handle precision up to seven decimal places for seconds, providing accurate time representation.
TIME() Function
can be used to convert various date-time formats to a time-only format, which is useful in scenarios where only the time component is required.- The function simplifies queries that involve time comparisons or calculations by isolating the time component from date-time values.
- It is commonly used in reporting, scheduling, and logging systems where operations based on time values are necessary.
Conclusion
The TIME()
function in MySQL is a valuable tool for extracting and working with the time component of datetime values. It allows developers to isolate the time from date-time expressions, making it particularly useful in time-specific operations such as filtering, logging, or reporting.