Syntax error Convert number INT in minutes to TIME in MySQL?

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)
Updated on: 2019-07-30T22:30:25+05:30

7K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements