{"id":2871,"date":"2018-07-03T17:25:01","date_gmt":"2018-07-04T00:25:01","guid":{"rendered":"https:\/\/database.guide\/?p=2871"},"modified":"2021-01-28T14:10:36","modified_gmt":"2021-01-28T04:10:36","slug":"time_format-examples-mysql","status":"publish","type":"post","link":"https:\/\/database.guide\/time_format-examples-mysql\/","title":{"rendered":"TIME_FORMAT() Examples – MySQL"},"content":{"rendered":"

In MySQL<\/a>, you can use the TIME_FORMAT()<\/code> function to format a time value.<\/p>\n

This function works just like the DATE_FORMAT()<\/code><\/a> function, except that the value can only be formatted in\u00a0hours, minutes, seconds, and microseconds.<\/p>\n

See the table at the bottom of this article for a list of format specifiers that can be used with this function.<\/p>\n

<\/p>\n

Syntax<\/h2>\n

The syntax goes like this:<\/p>\n

TIME_FORMAT(time,format)\r\n<\/pre>\n
Where time<\/code> is the time value you want formatted, and format<\/code> is the format string (this determines how the time value is actually formatted).<\/p>\n
Example 1 – Basic Usage<\/h2>\n
Here’s an example to demonstrate.<\/p>\n
SELECT TIME_FORMAT('14:35:27', '%r') AS 'Result';\r\n<\/pre>\n
Result:<\/p>\n
+-------------+\r\n| Result      |\r\n+-------------+\r\n| 02:35:27 PM |\r\n+-------------+\r\n<\/pre>\n
This example uses the %r<\/code> format specifier, which formats the time as 12-hour (hh:mm:ss followed by AM or PM).<\/p>\n
Example 2 – More Specific Formatting<\/h2>\n
If using a more general format specifier like in the previous example isn’t suitable, there are various other format specifiers that can help you build the format you require.<\/p>\n
For example, if you like the previous format, but you don’t want the seconds to be returned, you could do this:<\/p>\n
SELECT TIME_FORMAT('14:35:27', '%h:%i %p') AS 'Result';\r\n<\/pre>\n
Result:<\/p>\n
+----------+\r\n| Result   |\r\n+----------+\r\n| 02:35 PM |\r\n+----------+\r\n<\/pre>\n
Example 3 – 24 Hour Time<\/h2>\n
There are also format specifiers for 24 hour time. Here’s the easiest way to format the time using 24 hour time.<\/p>\n
SELECT TIME_FORMAT('14:35:27', '%T') AS 'Result';\r\n<\/pre>\n
Result:<\/p>\n
+----------+\r\n| Result   |\r\n+----------+\r\n| 14:35:27 |\r\n+----------+\r\n<\/pre>\n
Example 4 – Microseconds<\/h2>\n
Here’s an example that includes the fractional seconds part in the result:<\/p>\n
SELECT TIME_FORMAT('14:35:27', '%H:%i:%s.%f') AS 'Result';\r\n<\/pre>\n
Result:<\/p>\n
+-----------------+\r\n| Result          |\r\n+-----------------+\r\n| 14:35:27.000000 |\r\n+-----------------+\r\n<\/pre>\n
Of course, we can also include microseconds in the initial time value:<\/p>\n
SELECT TIME_FORMAT('14:35:27.123456', '%H:%i:%s.%f') AS 'Result';\r\n<\/pre>\n
Result:<\/p>\n
+-----------------+\r\n| Result          |\r\n+-----------------+\r\n| 14:35:27.123456 |\r\n+-----------------+\r\n<\/pre>\n
Example 5 – Elapsed Time<\/h2>\n
The time data type is not limited to 24 hour time. It can also be used to represent elapsed time. When using this function for elapsed time, you should be aware of how the hour format specifiers work with time values outside the range 0 to 23.<\/p>\n
The MySQL documentation states<\/a> the following:<\/p>\n
If the time value contains an hour part that is greater than 23<\/code>, the %H<\/code> and %k<\/code> hour format specifiers produce a value larger than the usual range of 0..23<\/code>. The other hour format specifiers produce the hour value modulo 12<\/code>.<\/p><\/blockquote>\n
Here’s an example to demonstrate:<\/p>\n
SELECT \r\n    TIME_FORMAT('24:00:00', '%H %k %h %I %l') AS '24',\r\n    TIME_FORMAT('48:00:00', '%H %k %h %I %l') AS '48',\r\n    TIME_FORMAT('100:00:00', '%H %k %h %I %l') AS '100',\r\n    TIME_FORMAT('500:00:00', '%H %k %h %I %l') AS '500';\r\n<\/pre>\n
Result:<\/p>\n
+----------------+----------------+-----------------+-----------------+\r\n| 24             | 48             | 100             | 500             |\r\n+----------------+----------------+-----------------+-----------------+\r\n| 24 24 12 12 12 | 48 48 12 12 12 | 100 100 04 04 4 | 500 500 08 08 8 |\r\n+----------------+----------------+-----------------+-----------------+\r\n<\/pre>\n
Format Specifiers<\/h2>\n
The following specifiers can be used to specify the return format. These are a subset of those available with the DATE_FORMAT()<\/code><\/a> function.\u00a0The format value must start with a percentage sign (%<\/code>).<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Specifier<\/th>\nDescription<\/th>\n<\/tr>\n<\/thead>\n
%f<\/code><\/td>\nMicroseconds (000000<\/code>..999999<\/code>)<\/td>\n<\/tr>\n
%H<\/code><\/td>\nHour (00<\/code>..23<\/code>)<\/td>\n<\/tr>\n
%h<\/code><\/td>\nHour (01<\/code>..12<\/code>)<\/td>\n<\/tr>\n
%I<\/code><\/td>\nHour (01<\/code>..12<\/code>)<\/td>\n<\/tr>\n
%i<\/code><\/td>\nMinutes, numeric (00<\/code>..59<\/code>)<\/td>\n<\/tr>\n
%k<\/code><\/td>\nHour (0<\/code>..23<\/code>)<\/td>\n<\/tr>\n
%l<\/code><\/td>\nHour (1<\/code>..12<\/code>)<\/td>\n<\/tr>\n
%p<\/code><\/td>\nAM<\/code> or PM<\/code><\/td>\n<\/tr>\n
%r<\/code><\/td>\nTime, 12-hour (hh:mm:ss<\/code> followed by AM<\/code> or PM<\/code>)<\/td>\n<\/tr>\n
%S<\/code><\/td>\nSeconds (00<\/code>..59<\/code>)<\/td>\n<\/tr>\n
%s<\/code><\/td>\nSeconds (00<\/code>..59<\/code>)<\/td>\n<\/tr>\n
%T<\/code><\/td>\nTime, 24-hour (hh:mm:ss<\/code>)<\/td>\n<\/tr>\n
%%<\/code><\/td>\nA literal %<\/code> character<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
In MySQL, you can use the TIME_FORMAT() function to format a time value. This function works just like the DATE_FORMAT() function, except that the value can only be formatted in\u00a0hours, minutes, seconds, and microseconds. See the table at the bottom of this article for a list of format specifiers that can be used with this … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[68,115,93],"class_list":["post-2871","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-date-functions","tag-dates","tag-functions"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2871","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=2871"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2871\/revisions"}],"predecessor-version":[{"id":6843,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2871\/revisions\/6843"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=2871"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=2871"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=2871"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}