MySQL TIME_FORMAT Function

MySQL TIME_FORMAT Function

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

ParameterDescription
timeThis is the first parameter and required. It is the time value you want formatted.
formatThis 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 (%).

SpecifierDescription
%fMicroseconds (000000..999999)
%HHour (00..23)
%hHour (01..12)
%IHour (01..12)
%iMinutes, numeric (00..59)
%kHour (0..23)
%lHour (1..12)
%pAM or PM
%rTime, 12-hour (hh:mm:ss followed by AM or PM)
%SSeconds (00..59)
%sSeconds (00..59)
%TTime, 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    |
 +---------------------+  

Recommended Tutorial

AuthorAdmin

My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of Tutsmake.com. I like writing tutorials and tips that can help other developers. I share tutorials of PHP, Python, Javascript, JQuery, Laravel, Livewire, Codeigniter, Node JS, Express JS, Vue JS, Angular JS, React Js, MySQL, MongoDB, REST APIs, Windows, Xampp, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL and Bootstrap from a starting stage. As well as demo example.

Leave a Reply

Your email address will not be published. Required fields are marked *