Convert number INT in minutes to TIME in MySQL?

To convert number INT in minutes to TIME in MySQL, you can use SEC_TO_TIME() function.

The syntax is as follows

select SEC_TO_TIME(yourIntColumnName*60) AS `anyAliasName` from yourTableName;

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

mysql> create table convertNumberToMinute
   -> (
   -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> NumberToMinute int
   -> );
Query OK, 0 rows affected (0.79 sec)

Insert some records in the table using insert command.

The query is as follows

mysql> insert into convertNumberToMinute(NumberToMinute) values(60);
Query OK, 1 row affected (0.12 sec)
mysql> insert into convertNumberToMinute(NumberToMinute) values(70);
Query OK, 1 row affected (0.21 sec)
mysql> insert into convertNumberToMinute(NumberToMinute) values(80);
Query OK, 1 row affected (0.15 sec)
mysql> insert into convertNumberToMinute(NumberToMinute) values(90);
Query OK, 1 row affected (0.13 sec)
mysql> insert into convertNumberToMinute(NumberToMinute) values(100);
Query OK, 1 row affected (0.22 sec)
mysql> insert into convertNumberToMinute(NumberToMinute) values(110);
Query OK, 1 row affected (0.07 sec)
mysql> insert into convertNumberToMinute(NumberToMinute) values(120);
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement.

The query is as follows

mysql> select *from convertNumberToMinute;

The following is the output

+----+----------------+
| Id | NumberToMinute |
+----+----------------+
| 1  | 60             |
| 2  | 70             |
| 3  | 80             |
| 4  | 90             |
| 5  | 100            |
| 6  | 110            |
| 7  | 120            |
+----+----------------+
7 rows in set (0.00 sec)

Here is the query to convert number INT in minutes to TIME in MySQL

mysql> select SEC_TO_TIME(NumberToMinute*60) AS `MinuteDemo` from convertNumberToMinute;

The following is the output

+------------+
| MinuteDemo |
+------------+
| 01:00:00   |
| 01:10:00   |
| 01:20:00   |
| 01:30:00   |
| 01:40:00   |
| 01:50:00   |
| 02:00:00   |
+------------+
7 rows in set (0.00 sec)

Here is the query to display ‘NumberToMinute’ column

mysql> select NumberToMinute, SEC_TO_TIME(NumberToMinute*60) AS `MinuteDemo` from convertNumberToMinute;

The following is the output

+----------------+------------+
| NumberToMinute | MinuteDemo |
+----------------+------------+
| 60             | 01:00:00   |
| 70             | 01:10:00   |
| 80             | 01:20:00   |
| 90             | 01:30:00   |
| 100            | 01:40:00   |
| 110            | 01:50:00   |
| 120            | 02:00:00   |
+----------------+------------+
7 rows in set (0.00 sec)

Ankith Reddy

e

Updated on: 30-Jul-2019

5K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started