Mysql Time_format Function

MySQL: TIME_FORMAT Function

This tutorial explains how to use the MySQL TIME_FORMAT function with syntax and examples.

Description

The MySQL TIME_FORMAT function formats a time as specified by a format mask.

Syntax

The syntax for the TIME_FORMAT function in MySQL is:

TIME_FORMAT( time, format_mask )

Parameters or Arguments

time

The time to format.

format_mask

The format to apply to time. The following is a list of options for the format_mask parameter. These parameters can be used in many combinations.

Value Description
%f Microseconds (000000 to 999999) %f is available starting in MySQL 4.1.1
%H Hour (00 to 23 generally, but can be higher)
%h Hour (00 to 12)
%I Hour (00 to 12)
%i Minutes (00 to 59)
%p AM or PM
%r Time in 12 hour AM or PM format (hh:mm:ss AM/PM)
%S Seconds (00 to 59)
%s Seconds (00 to 59)
%T Time in 24 hour format (hh:mm:ss)

Note

  • The TIME_FORMAT function only formats the hours, minutes, seconds, and microseconds found in a time value.
  • See also the DATE_FORMAT function.

Applies To

The TIME_FORMAT function can be used in the following versions of MySQL:

  • MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23

Example

Let's look at some MySQL TIME_FORMAT function examples and explore how to use the TIME_FORMAT function in MySQL.

For example:

mysql> SELECT TIME_FORMAT('15:02:28', '%H %i %s');
Output: '15 02 28'

mysql> SELECT TIME_FORMAT('15:02:28', '%h:%i:%s %p');
Output: '03:02:28 PM'

mysql> SELECT TIME_FORMAT('15:02:28', '%h:%i%p');
Output: '03:02PM'

mysql> SELECT TIME_FORMAT('17:42:03.000001', '%r');
Output: '05:42:03 PM'

mysql> SELECT TIME_FORMAT('17:42:03.000001', '%T');
Output: '17:42:03'

mysql> SELECT TIME_FORMAT('07:42:03.000001', '%f');
Output: '000001'