MySQL query to convert a string like “1h 15 min” into 75 minutes?

You can use str_to_date() for this conversion. Let us first create a table −

mysql> create table DemoTable
   (
   stringDate varchar(100)
   );
Query OK, 0 rows affected (0.86 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('1h 15 min');
Query OK, 1 row affected (0.24 sec)
mysql> insert into DemoTable values('2h 30 min');
Query OK, 1 row affected (0.21 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

Output

+------------+
| stringDate |
+------------+
| 1h 15 min  |
| 2h 30 min  |
+------------+
2 rows in set (0.00 se

Following is the query to convert string a string like "1 h 15 min" into 75 i.e. 75 minutes −

mysql> select
time_to_sec(str_to_date(stringDate, '%l h %i min')) / 60 second
from DemoTable
having second is not null
union all
select
time_to_sec(str_to_date(stringDate, '%i min')) / 60 second
from DemoTable
having second is not null;

Output

+----------+
| second   |
+----------+
| 75.0000  |
| 150.0000 |
+----------+
2 rows in set, 2 warnings (0.04 sec)

Chandu yadav

Published on 14-Jun-2019 07:43:22

• Related Questions & Answers
• Can we use “LIKE concat()” in a MySQL query?
• How to query MongoDB with “like”?
• How to query MongoDB similar to “like” ?
• MongoDB query to search for string like “@email” in the field values
• MySQL query to convert a string into a month (Number)?
• Maintaining order in MySQL “IN” query?
• MySQL query error with a table named “order”?
• Display minutes with SimpleDateFormat(“m”) in Java
• Can we get records “Jone Deo” or “Deo Jone” with a single MySQL query?
• MySQL query for sorting on a columns partial value like number in “John_120 “
• “Toggle” query in MongoDB?
• MySQL: delete all rows containing string “foo” in sample table “bar”?
• Write a MySQL query equivalent to “SHOW TABLES” in sorted order?
• How to replace “and” in a string with “&” in R?
• How to convert varchar “time” to real time in MySQL?