TIME_FORMAT MySQL Function – Example

TIME_FORMAT MySQL Function – Example

MySQL – TIME_FORMAT function. In this tutorial, we would love to share with you how to use TIME_FORMAT() function with example. Also, we will explain TIME_FORMAT() function definition, syntax, and various 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

  1. MySQL Tutorial With Examples
  2. PHP Date and Time functions tutorial
  3. MySQL: DATE() And TIME() Functions 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, Javascript, JQuery, Laravel, Livewire, Codeigniter, Vue JS, Angular JS, React Js, WordPress, 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 *