MySQL - TIME_FORMAT() Function

---

The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. MySQL provides a set of functions to manipulate these values.

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.

Syntax

Following is the syntax of the above function –

TIME_FORMAT(date,format);

Example 1

Following example demonstrates the usage of the TIME_FORMAT() function. It prints the weekday (full), month (full) and the year of the given date–

mysql> SELECT TIME_FORMAT('10:30:35', '%H Hours %i Minues');
+-----------------------------------------------+
| TIME_FORMAT('10:30:35', '%H Hours %i Minues') |
+-----------------------------------------------+
| 10 Hours 30 Minues                            |
+-----------------------------------------------+
1 row in set (0.00 sec)

Example 2

You can also pass datetime value to this function.

mysql> SELECT TIME_FORMAT('2015-09-05 12:09:40', '%H');
+------------------------------------------+
| TIME_FORMAT('2015-09-05 12:09:40', '%H') |
+------------------------------------------+
|                                       12 |
+------------------------------------------+
1 row in set (0.00 sec)

Example 3

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

mysql> SELECT TIME_FORMAT('09', '%T');
+-------------------------+
| TIME_FORMAT('09', '%T') |
+-------------------------+
| 00:00:09                |
+-------------------------+
1 row in set (0.00 sec)

Example 4

If you don’t specify the seconds value this function considers it as 0.

mysql> SELECT TIME_FORMAT('10:12', '%T');
+----------------------------+
| TIME_FORMAT('10:12', '%T') |
+----------------------------+
| 10:12:00                   |
+----------------------------+
1 row in set (0.00 sec)

Example 5

Following query prints the given time in 24 hours format -

mysql> SELECT TIME_FORMAT('19:45:00', '%r');
+-------------------------------+
| TIME_FORMAT('19:45:00', '%r') |
+-------------------------------+
| 07:45:00 PM                   |
+-------------------------------+
1 row in set (0.00 sec)

Example 6

Suppose we have created a table named SubscribersData with 5 records in it using the following queries –

mysql> CREATE TABLE SubscribersData(
	SubscriberName VARCHAR(255),
	PackageName VARCHAR(255),
	SubscriptionDate date,
	SubscriptionTime time
);
insert into SubscribersData values('Raja', 'Premium', Date('2020-10-21'), Time('20:53:49'));
insert into SubscribersData values('Roja', 'Basic', Date('2020-11-26'), Time('10:13:19'));
insert into SubscribersData values('Puja', 'Moderate', Date('2021-03-07'), Time('05:43:20'));
insert into SubscribersData values('Vanaja', 'Basic', Date('2021-02-21'), Time('16:36:39'));
insert into SubscribersData values('Jalaja', 'Premium', Date('2021-01-30'), Time('12:45:45'));

In the following example we are trying to format the SubscriptionDate and SubscriptionTime columns as a single timestamp according to the USAstandard –

mysql> SELECT SubscriberName, PackageName,
TIME_FORMAT(SubscriptionTime,
GET_FORMAT(TIMESTAMP, 'USA')) as Time
FROM SubscribersData;
+----------------+-------------+---------------------+
| SubscriberName | PackageName | TIMESTAMP           |
+----------------+-------------+---------------------+
| Raja           | Premium     | 0000-00-00 20.53.49 |
| Roja           | Basic       | 0000-00-00 10.13.19 |
| Puja           | Moderate    | 0000-00-00 05.43.20 |
| Vanaja         | Basic       | 0000-00-00 16.36.39 |
| Jalaja         | Premium     | 0000-00-00 12.45.45 |
+----------------+-------------+---------------------+
5 rows in set (0.15 sec)

Following query formats the SubscriptionTime column according to the USA standards –

mysql> SELECT SubscriberName, PackageName,
TIME_FORMAT(SubscriptionTime,
GET_FORMAT(TIMESTAMP, 'USA')) as Time
FROM SubscribersData;
+----------------+-------------+---------------------+
| SubscriberName | PackageName | Time                |
+----------------+-------------+---------------------+
| Raja           | Premium     | 0000-00-00 20.53.49 |
| Roja           | Basic       | 0000-00-00 10.13.19 |
| Puja           | Moderate    | 0000-00-00 05.43.20 |
| Vanaja         | Basic       | 0000-00-00 16.36.39 |
| Jalaja         | Premium     | 0000-00-00 12.45.45 |
+----------------+-------------+---------------------+
5 rows in set (0.00 sec)

Example 7

Let us create another table with name Sales in MySQL database using CREATE statement as follows –

mysql> CREATE TABLE sales(
	ID INT,
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchDate date,
	DispatchTime time,
	Price INT,
	Location VARCHAR(255)
);
Query OK, 0 rows affected (2.22 sec)

Now, we will insert 5 records in Sales table using INSERT statements:

insert into sales values (1, 'Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 'Hyderabad');
insert into sales values (2, 'Earphones', 'Roja', DATE('2019-05-01'), TIME('11:00:00'), 2000, 'Vishakhapatnam');
insert into sales values (3, 'Mouse', 'Puja', DATE('2019-03-01'), TIME('10:59:59'), 3000, 'Vijayawada');
insert into sales values (4, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 'Chennai');
insert into sales values (5, 'Headset', 'Jalaja', DATE('2019-04-06'), TIME('11:08:59'), 6000, 'Goa');

Following is another example of this function —

mysql> SELECT ProductName, CustomerName, DispatchDate, Price, TIME_FORMAT(DispatchTime, '%r') FROM sales;
+-------------+--------------+--------------+-------+---------------------------------+
| ProductName | CustomerName | DispatchDate | Price | TIME_FORMAT(DispatchTime, '%r') |
+-------------+--------------+--------------+-------+---------------------------------+
| Key-Board   | Raja         | 2019-09-01   | 7000  | 11:00:00 AM                     |
| Earphones   | Roja         | 2019-05-01   | 2000  | 11:00:00 AM                     |
| Mouse       | Puja         | 2019-03-01   | 3000  | 10:59:59 AM                     |
| Mobile      | Vanaja       | 2019-03-01   | 9000  | 10:10:52 AM                     |
| Headset     | Jalaja       | 2019-04-06   | 6000  | 11:08:59 AM                     |
+-------------+--------------+--------------+-------+---------------------------------+
5 rows in set (0.16 sec)

Format String

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

• %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)
• %T – Time, 24-hour (hh:mm:ss)