How to use the MySQL TIME_FORMAT() function

In this article, we will learn how to use the MySQL TIME_FORMAT() function, which returns the time value formatted according to a specified format.

Posted on

In this article, we will learn how to use the MySQL TIME_FORMAT() function, which returns the time value formatted according to a specified format. 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 times and formats.

Syntax

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

TIME_FORMAT(time, format)

The time parameter can be any valid time expression, or a string that can be converted to a time value. The format parameter can be any string expression that specifies the format of the time value. The format parameter can contain any of the time format specifiers that are supported by MySQL. If either parameter is NULL, the function returns NULL. The TIME_FORMAT() function returns a string that represents the time value formatted according to the given format. For example, TIME_FORMAT('10:02:34', '%h:%i %p') returns ‘10:02 AM’, which is the time value in 12-hour format.

Examples

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

Example 1: Format a time value in 12-hour format

We can use the TIME_FORMAT() function to format a time value in 12-hour format, with hour, minute, and AM/PM indicator. For example:

SELECT TIME_FORMAT('10:02:34', '%h:%i %p') AS result;

This query will format the time value ‘10:02:34’ in 12-hour format, with hour, minute, and AM/PM indicator. The query will return ‘10:02 AM’, which is the formatted time value.

Example 2: Format a time value in 24-hour format

We can use the TIME_FORMAT() function to format a time value in 24-hour format, with hour, minute, and second. For example:

SELECT TIME_FORMAT('10:02:34', '%H:%i:%s') AS result;

This query will format the time value ‘10:02:34’ in 24-hour format, with hour, minute, and second. The query will return ‘10:02:34’, which is the formatted time value.

Example 3: Format a time value with fractional seconds

We can use the TIME_FORMAT() function to format a time value with fractional seconds, such as milliseconds or microseconds. For example:

SELECT TIME_FORMAT('10:02:34.123456', '%H:%i:%s.%f') AS result;

This query will format the time value ‘10:02:34.123456’ with fractional seconds, up to six digits. The query will return ‘10:02:34.123456’, which is the formatted time value.

Example 4: Format a time value with different separators

We can use the TIME_FORMAT() function to format a time value with different separators, such as dots, dashes, or slashes. For example:

SELECT TIME_FORMAT('10:02:34', '%H.%i.%s') AS result;

This query will format the time value ‘10:02:34’ with dots as separators, instead of colons. The query will return ‘10.02.34’, which is the formatted time value.

Example 5: Format a time value with different literals

We can use the TIME_FORMAT() function to format a time value with different literals, such as words, symbols, or spaces. For example:

SELECT TIME_FORMAT('10:02:34', 'The time is %H hours %i minutes %s seconds') AS result;

This query will format the time value ‘10:02:34’ with different literals, such as words, symbols, or spaces. The query will return ‘The time is 10 hours 02 minutes 34 seconds’, which is the formatted time value.

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

  • TIME(): This function returns the time part of a date or datetime value, or a string that can be converted to a time value. For example, TIME('2023-01-15 10:02:34') returns ‘10:02:34’.
  • TIME_TO_SEC(): This function converts a time value to a number of seconds. For example, TIME_TO_SEC('10:02:34') returns 36154.
  • SEC_TO_TIME(): This function converts a number of seconds to a time value. For example, SEC_TO_TIME(36154) returns ‘10:02:34’.
  • DATE_FORMAT(): This function returns the date or datetime value formatted according to a specified format. For example, DATE_FORMAT('2023-01-15 10:02:34', '%Y-%m-%d %H:%i:%s') returns ‘2023-01-15 10:02:34’.
  • STR_TO_DATE(): This function converts a string to a date or datetime value, based on a specified format. For example, STR_TO_DATE('15/01/2023 10:02:34', '%d/%m/%Y %H:%i:%s') returns ‘2023-01-15 10:02:34’.

Conclusion

In this article, we learned how to use the MySQL TIME_FORMAT() function, which returns the time value formatted according to a specified format. 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 times and formats.