Remove seconds from time field in MySQL?

You need to use TIME_FORMAT() to remove seconds from time field. The syntax is as follows:

SELECT TIME_FORMAT(yourColumnName1, "%H:%i") AS anyVariableName,
TIME_FORMAT(yourColumnName2, "%H:%i") AS anyVariableName
FROM yourTableName;

To understand the above syntax, let us create a table. The query to create a table is as follows:

mysql> create table removeSecondsFromTime
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> StartTime time,
   -> EndTime time,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.71 sec)

Insert some records in the table using insert command. The query is as follows:

mysql> insert into removeSecondsFromTime(StartTime,EndTime) values('10:20:45','11:21:40');
Query OK, 1 row affected (0.43 sec)
mysql> insert into removeSecondsFromTime(StartTime,EndTime) values('12:30:55','13:20:21');
Query OK, 1 row affected (0.31 sec)
mysql> insert into removeSecondsFromTime(StartTime,EndTime) values('14:40:57','15:55:02');
Query OK, 1 row affected (0.24 sec)

Display all records from the table using select statement. The query is as follows:

mysql> select *from removeSecondsFromTime;

The following is the output;

+----+-----------+----------+
| Id | StartTime | EndTime  |
+----+-----------+----------+
|  1 | 10:20:45  | 11:21:40 |
|  2 | 12:30:55  | 13:20:21 |
|  3 | 14:40:57  | 15:55:02 |
+----+-----------+----------+
3 rows in set (0.00 sec)

Here is the query to remove seconds from time field:

mysql> select TIME_FORMAT(StartTime, "%H:%i") as StartingTime,
   -> TIME_FORMAT(EndTime, "%H:%i") as EndingTime
   -> from removeSecondsFromTime;

The following is the output;

+--------------+------------+
| StartingTime | EndingTime |
+--------------+------------+
| 10:20        | 11:21      |
| 12:30        | 13:20      |
| 14:40        | 15:55      |
+--------------+------------+
3 rows in set (0.00 sec)

Arjun Thakur

e

Updated on: 30-Jul-2019

2K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started