How to use the MySQL TIME() function

In this article, we will learn how to use the MySQL TIME() function, which returns the time part of a date or datetime value, or a string that can be converted to a time value. We will also see some examples of how to use this function in different situations, and explore some related functions that can be helpful for working with dates, datetimes, and times.

Syntax

The syntax of the TIME() function is as follows:

TIME(expr)

The expr parameter can be any valid date, datetime, or time expression, or a string that can be converted to a date, datetime, or time value. If the expr parameter is NULL, the function returns NULL. The TIME() function returns a time value that represents the time part of the given expression. The format of the time value is ‘HH:MM:SS’. For example, TIME('2023-01-15 10:02:34') returns ‘10:02:34’, which is the time part of the datetime value.

Examples

Let’s see some examples of how to use the TIME() function in MySQL.

Example 1: Get the time part of a date value

We can use the TIME() function to get the time part of a date value. For example:

SELECT TIME('2023-01-15') AS result;

This query will return the time part of the date value ‘2023-01-15’. The query will return ‘00:00:00’, which is the default time for a date value.

Example 2: Get the time part of a datetime value

We can use the TIME() function to get the time part of a datetime value. For example:

SELECT TIME('2023-01-15 10:02:34') AS result;

This query will return the time part of the datetime value ‘2023-01-15 10:02:34’. The query will return ‘10:02:34’, which is the time part of the datetime value.

Example 3: Get the time part of a time value

We can use the TIME() function to get the time part of a time value. For example:

SELECT TIME('10:02:34') AS result;

This query will return the time part of the time value ‘10:02:34’. The query will return ‘10:02:34’, which is the same as the time value.

Example 4: Get the time part of a string value

We can use the TIME() function to get the time part of a string value that can be converted to a date, datetime, or time value. For example:

SELECT TIME('January 15, 2023 10:02:34') AS result;

This query will return the time part of the string value ‘January 15, 2023 10:02:34’ that can be converted to a datetime value. The query will return ‘10:02:34’, which is the time part of the datetime value.

Example 5: Get the time part of an invalid value

We can use the TIME() function to get the time part of an invalid value that cannot be converted to a date, datetime, or time value. For example:

SELECT TIME('Hello World') AS result;

This query will try to return the time part of the invalid value ‘Hello World’ that cannot be converted to a date, datetime, or time value. The query will return NULL, since the function cannot parse the value.

There are some other functions that are related to the TIME() function, and can be useful for working with dates, datetimes, and times. Here are some of them:

DATE(): This function returns the date part of a date or datetime value. For example, DATE('2023-01-15 10:02:34') returns ‘2023-01-15’.
DATETIME(): This function returns the datetime value of a date or datetime value, or a string that can be converted to a datetime value. For example, DATETIME('2023-01-15 10:02:34') returns ‘2023-01-15 10:02:34’.
TIMESTAMP(): This function returns the timestamp value of a date, datetime, or timestamp value, or a string that can be converted to a timestamp value. For example, TIMESTAMP('2023-01-15 10:02:34') returns ‘2023-01-15 10:02:34’.
HOUR(): This function returns the hour of a time or datetime value. For example, HOUR('10:02:34') returns 10.
MINUTE(): This function returns the minute of a time or datetime value. For example, MINUTE('10:02:34') returns 2.
SECOND(): This function returns the second of a time or datetime value. For example, SECOND('10:02:34') returns 34.

Conclusion

In this article, we learned how to use the MySQL TIME() function, which returns the time part of a date or datetime value, or a string that can be converted to a time value. We also saw some examples of how to use this function in different situations, and explored some related functions that can be helpful for working with dates, datetimes, and times.