MySQL TIME_FORMAT()

By /
TIME FORMAT()

In this tutorial, we will learn about the MySQL TIME_FORMAT() function. The time value in MySQL is of the format “HH:MM:SS” and in the 24 hour format.

However, such a time value may be difficult to read for some people – who prefer the 12 hour format accompanied by AM/PM.

Some people may not prefer to see the seconds value at all, they’d just want to see the time value which shows the hour and minute value. This is where the TIME_FORMAT() function comes into play. 

The TIME_FORMAT() function formats a time value with a given specified format. You may also use MySQL TIME_FORMAT() on datetime values – however, this may cause information loss as TIME_FORMAT() will return only the formatted time value and skip the date value altogether.

Syntax of MySQL TIME_FORMAT()

TIME_FORMAT(time, “format”)Code language: SQL (Structured Query Language) (sql)

Where

  • ‘time’ is the time value that needs to be formatted and,
  • ‘format’ is the format to be used. This value should be in double-quotes.

The ‘format’ can be one or a combination of the following –

  • %f – To show microseconds value (000000 to 999999)
  • %H – To show only the hour value in 24-hour format (00 to 23)
  • %h – To show only the hour value in 12-hour format (00 to 12)
  • %I – To show only the hour value in 12-hour format (00 to 12)
  • %i – To show only the minutes value (00 to 59)
  • %p – To display the AM or PM value
  • %r – To display time in 12-hour AM or PM format (hh:mm:ss AM/PM)
  • %S – To show only the seconds value (00 to 59)
  • %s – To show only the seconds value (00 to 59)
  • %T – To display time in 24-hour format (hh:mm:ss)

Examples of MySQL TIME_FORMAT()

Let us start with a basic example. Let us format the time value – ’15:06:22′ to display it in a 12-hour format of HH:MM:SS and display the AM/PM value. If we look at the possible ‘format’ values, we see the format “%r” fits our needs. Let us write a query for it using the SELECT statement and aliases. The query is –

SELECT TIME_FORMAT('15:06:22', "%r") AS Time;Code language: SQL (Structured Query Language) (sql)

And the output is –

Time Format Basic Example

Displaying Only AM/PM Value

For the time value – ’15:06:22′, let us display only its AM or PM value. We can do so using the format “%p”. The query is –

SELECT TIME_FORMAT('15:06:22', "%p") AS Time;Code language: SQL (Structured Query Language) (sql)

And the output is –

Time Format Am Pm

Using Combination of Formats

For the time value – ’15:06:22′, let us display the time in the format “HH:MM AM/PM”. For this, we will need a combination of the format parameters. For the hour values, we can use %H. To display the minutes’ value, we will use %i and for the AM/PM value, we will use %p. We will specify a colon between %H and %i. The query is –

 SELECT TIME_FORMAT('15:06:22', "%H:%i %p") AS Time;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL Time Format Specific Formatting

Microseconds With MySQL TIME_FORMAT()

For the time value – ’15:06:22′, let us display the time in the format “HH:MM:SS.ssssss AM/PM”. For this, we will need a combination of the format parameters again. For the hour values, we can use %H. To display the minutes’ value, we will use %i. To display the seconds’ value, we will use %s and for microseconds, we will use %f. For the AM/PM value, we will use %p. We will specify colons between %H, %i and %s and a dot between %s and %f. The query is –

SELECT TIME_FORMAT('15:06:22', "%H:%i:%s.%f %p") AS Time;Code language: SQL (Structured Query Language) (sql)

The output is –

MySQL Time Format Microseconds

MySQL TIME_FORMAT() With CURTIME()

Next, let us use the CURTIME() function with MySQL TIME_FORMAT(). We will display the current time in a 12-hour “HH:MM AM/PM” format. The query is – 

SELECT TIME_FORMAT(CURTIME(), "%h:%i %p") AS Time;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL Time Format Curtime

Working With Tables

Consider the below BusStop Table.

Date Add Busstop Table
BusStop Table

Let us make the date and time of the above table more readable. Let us write a query that displays the bus number, name, start and end location, date of departure, and time of departure in the 12-hour “HH:MM:SS AM/PM” format. The query for it is –

SELECT Bus_No, Name, Start, End, 
DATE(DepartureTime) AS Date, 
TIME_FORMAT(DepartureTime,"%r") AS Time FROM BusStop;Code language: SQL (Structured Query Language) (sql)

And the output is –

Time Format Table Example

Conclusion

Formatting time values is a very important operation in MySQL and is widely used in a lot of use cases – especially when we have to make the time value presentable for the user. I would highly encourage you to practice a couple of examples of this function.

References