
- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- 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?
Advertisements