MySQL TIME_FORMAT function; In this tutorial, we would love to share with you how to use TIME_FORMAT() function with the help of examples.
The MySQL TIME_FORMAT function works just like the DATE_FORMAT () function, moreover this value can only be formatted in hours, minutes, seconds, and microseconds.
MySQL TIME_FORMAT() function
Definition:- The TIME_FORMAT() function is inbuilt Mysql function, which is used to formats a time value by a specific time.
Syntax
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 – Basic Example
Here, we will take an example for demonstrate.
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 – Format with 24 Hour Time
Here we will take a second example with 24 hour time format.
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 – Without Second Format
Let’s take an example without seconds in time. Sometimes you need to show time without seconds, so you can use the below query for that:
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 – Fractional Seconds Format
Let’s take an example with fractional seconds in time. Sometimes you need to show time with fractional seconds, so you can use the below query for that:
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 | +---------------------+
