TIME() Function in MySQL

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:

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".

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');

When you run the query:

SELECT *
FROM events
WHERE TIME(event_datetime) = '15:45:30';

Output:

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.

jana_sayantan

Follow

Improve

Article Tags :

• SQL
• Databases
• DBMS-SQL
• mysql