MySQL TIME_FORMAT function; In this tutorial, i am going to show you MySQL TIME_FORMAT() function with the help of examples.
MySQL TIME_FORMAT() function
MySQL TIME_FORMAT() converts a time in a formatted string using the format specifiers.
Syntax of MySQL TIME_FORMAT Function
The syntax of MySQL TIME_FORMAT Function; as follows:
TIME_FORMAT(time,format)
Parameters OF TIME_FORMAT() Function
| Parameter | Description |
|---|---|
| time | This is the first parameter and required. It is the time value you want formatted. |
| format | This is a second parameter and also required. format is the format string. |
Format Specifiers
Before we take examples of time_format() function. First of all, you need to know about the format specifiers. The given below following specifiers can be used to specify the return format. The format value must start with a percentage sign (%).
| Specifier | Description |
|---|---|
%f | Microseconds (000000..999999) |
%H | Hour (00..23) |
%h | Hour (01..12) |
%I | Hour (01..12) |
%i | Minutes, numeric (00..59) |
%k | Hour (0..23) |
%l | Hour (1..12) |
%p | AM or PM |
%r | Time, 12-hour (hh:mm:ss followed by AM or PM) |
%S | Seconds (00..59) |
%s | Seconds (00..59) |
%T | Time, 24-hour (hh:mm:ss) |
%% | A literal % character |
Example 1 – MySQL TIME_FORMAT Function
Let’s take an example using MySQL TIME_FORMAT function; as follows:
SELECT TIME_FORMAT('12:50:40', '%r') AS 'Output';
The output of the above query is the following:
+---------------------+ | Output | +---------------------+ | 12:50:40 PM | +---------------------+
In the above example, we have used the %r format specifier, which is used to formats the time value in 12-hour (hh:mm:ss with AM or PM).
Example 2 – MySQL TIME_FORMAT Function with 24 Hour Time
Let’s take an second example using MySQL TIME_FORMAT Function with 24 hour time format; as follows:
SELECT TIME_FORMAT('12:50:40', '%T') AS 'Output';
The output of the above query is the following:
+---------------------+ | Output | +---------------------+ | 12:50:40 PM | +---------------------+
Example 3 – MySQL TIME_FORMAT Function Without Second Format
Let’s take an example MySQL TIME_FORMAT function without seconds in time; as follows:
SELECT TIME_FORMAT('12:50:10', '%h:%i %p') AS 'Output';
The output of the above query is the following:
+---------------------+ | Output | +---------------------+ | 12:50: PM | +---------------------+
Example 4 – MySQL TIME_FORMAT Function with Fractional Seconds Format
Let’s take an example with fractional seconds in time; as follows:
SELECT TIME_FORMAT('12:50:10', '%H:%i:%s.%f') AS 'Output';
The output of the above query is the following:
+---------------------+ | Output | +---------------------+ | 12:50:10.000000 | +---------------------+
Conclusion
MySQL TIME_FORMAT function; In this tutorial, You have learned MySQL TIME_FORMAT() function with the help of examples.