MySQL - TIME_FORMAT() Function

The MYSQL TIME_FORMAT() function accepts date or date—time value and a format string (representing a desired date/time format) as parameters, formats the given date in the specified format and, returns the result.

MySQL holds some default formats for all the temporal datatypes with date and time values listed as follows:

Type Format
DATE YYYY-MM-DD
TIME HH:MM:SS
DATETIME YYYY-MM-DD HH:MI:SS
TIMESTAMP YYYY-MM-DD HH:MI:SS
YEAR YYYY

Format String

There are certain characters with predefined meaning using which you can create a format string. They are −

Type Format
%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)
%s Seconds (00..59)
%T Time, 24-hour (hh:mm:ss)

Syntax

Following is the syntax of MySQL TIME_FORMAT() function −

TIME_FORMAT(date,format);

Parameters

This method accepts two parameters. The same is described below −

  • date: The time value you want to format.

  • format: The format in which you want to display the time value.

Return value

This function returns a string representing the formatted time value based on the specified format.

Example

In the following query, we are using the TIME_FORMAT() function to format the time '10:30:35' into a custom display format, showing the hours and minutes −

SELECT TIME_FORMAT('10:30:35', '%H Hours %i Minutes') As Result;

Output

This will produce the following result −

Result
10 Hours 30 Minutes

Example

If we specify only one value this function assumes it as the seconds value.

SELECT TIME_FORMAT('09', '%T') As Result;

Output

Following is the output −

Result
00:00:09

Example

If we don't specify the seconds value this function considers it as 0.

SELECT TIME_FORMAT('10:12', '%T') As Result;

Output

Following is the output −

Result
10:12:00

Example

Following query prints the given time in 24 hours format −

SELECT TIME_FORMAT('19:45:00', '%r') As Result;

Output

Following is the output −

Result
07:45:00 PM

Example

In this example, we have created a table named SUBSCRIBERS using the following CREATE TABLE query −

CREATE TABLE SUBSCRIBERS(
   NAME varchar(255),
   PACKAGENAME varchar(255),
   SUBSCRIPTIONDATE date,
   SUBSCRIPTIONTIME time
);

Now, insert the following records into the SUBSCRIBERS table using the INSERT statement −

INSERT INTO SUBSCRIBERS VALUES
('Raja', 'Premium', Date('2020-10-21'), Time('20:53:49')),
('Roja', 'Basic', Date('2020-11-26'), Time('10:13:19')),
('Puja', 'Moderate', Date('2021-03-07'), Time('05:43:20')),
('Vanaja', 'Basic', Date('2021-02-21'), Time('16:36:39')),
('Jalaja', 'Premium', Date('2021-01-30'), Time('12:45:45'));

Execute the below query to fetch all the inserted records in the above-created table −

Select * From SUBSCRIBERS;

Following is the SUBSCRIBERS table −

NAME PACKAGENAME SUBSCRIPTIONDATE SUBSCRIPTIONTIME
Raja Premium 2020-10-21 20:53:49
Roja Basic 2020-11-26 10:13:19
Puja Moderate 2021-03-07 05:43:20
Vanaja Basic 2021-02-21 16:36:39
Jalaja Premium 2021-01-30 12:45:45

Here, we are using the MySQL TIME_FORMAT() function to convert the "SubscriptionTime" column into a formatted time string that displays hours, minutes, and seconds −

SELECT NAME, PACKAGENAME,
TIME_FORMAT(SubscriptionTime, '%H Hours %i Minutes %s Seconds')
As Result FROM SUBSCRIBERS;

Output

The output is displayed as follows −

NAME PACKAGENAME Result
Raja Premium 20 Hours 53 Minutes 49 Seconds
Roja Basic 10 Hours 13 Minutes 19 Seconds
Puja Moderate 05 Hours 43 Minutes 20 Seconds
Vanaja Basic 16 Hours 36 Minutes 39 Seconds
Jalaja Premium 12 Hours 45 Minutes 45 Seconds
Advertisements