[SOLVED] MySQL convert time minutes_seconds into seconds

Issue

Hi fellow overflow users, I’ve been searching for how to convert a given time given as HHhMMmssS ex: 28m54s or 1h22m3s or 27s into pure seconds with a MySQL command.

Output should be in pure seconds like:

from: 28m54s  -> 1734
from: 1h22m3s  ->  4923
from: 27s  ->  27

These values have been imported into a column called length.

If this can’t be done, the second best choice would be PHP. This is to a one of job so a copy paste answer can do 🙂

MariaDB structure

  `title` varchar(255) NOT NULL DEFAULT '',
  `embed` varchar(800) NOT NULL,
  `description` text NOT NULL,
  `keywords` varchar(255) NOT NULL,
  `length` int(11) NOT NULL DEFAULT '0',
  `source_thumb_url` varchar(255) NOT NULL,
  `record_num` int(11) NOT NULL AUTO_INCREMENT

MariaDB [csv_import]> SHOW COLUMNS FROM table_name;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| title            | varchar(255) | NO   |     | NULL    |       |
| embed            | varchar(800) | NO   |     | NULL    |       |
| description      | text         | NO   |     | NULL    |       |
| keywords         | varchar(255) | NO   |     | NULL    |       |
| length           | int(11)      | NO   |     | NULL    |       |
| source_thumb_url | varchar(255) | NO   |     | NULL    |       |
| record_num       | int(11)      | NO   | PRI | NULL    |       |
+------------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

Select string

SET @from = '`length`';
SELECT TIME_TO_SEC(STR_TO_DATE(
    CONCAT(IF(@from LIKE '%h%', '', '0h'), IF(@from LIKE '%m%', '', '0m'), IF(@from LIKE '%s%', '', '0s'), @from),
    '%kh%im%ss')) AS sec;

Result of using UPDTAE

UPDATE `database_name`.`table_name` SET `length` = SELECT TIME_TO_SEC(STR_TO_DATE(
    CONCAT(IF(`length` LIKE '%h%', '', '0h'), IF(`length` LIKE '%m%', '', '0m'), `length`),
    '%kh%im%ss'));

returns: Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘select time_to_sec(str_to_date(
CONCAT(IF(length like ‘%h%’, ”, ‘0h’), I’ at line 1

And made the changes on table length

+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| title            | varchar(255) | NO   |     | NULL    |                |
| embed            | varchar(800) | NO   |     | NULL    |                |
| description      | text         | NO   |     | NULL    |                |
| keywords         | varchar(255) | NO   |     | NULL    |                |
| length           | varchar(20)  | NO   |     | NULL    |                |
| source_thumb_url | varchar(255) | NO   |     | NULL    |                |
| record_num       | int(11)      | NO   | PRI | NULL    | auto_increment |
+------------------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

UPDATE to Strawberry’s answer

SELECT CASE WHEN `length` LIKE '%h%' 
            THEN TIME_TO_SEC(STR_TO_DATE(`length`,'%hh%im%ss')) 
            WHEN `length` LIKE '%m%' 
            THEN TIME_TO_SEC(STR_TO_DATE(`length`,'%im%ss')) 
            ELSE TIME_TO_SEC(STR_TO_DATE(`length`,'%ss')) 
            END X;

Error Code: 1054
Unknown column ‘length’ in ‘field list’

Solution

Use the following query utilizing time_to_sec and str_to_date.

select time_to_sec(str_to_date(
    CONCAT(IF(@from like '%h%', '', '0h'), IF(@from like '%m%', '', '0m'), @from),
    '%kh%im%ss')) as sec;

Demo:

mysql> set @from = '28m54s';
Query OK, 0 rows affected (0.00 sec)
mysql> select time_to_sec(str_to_date(
    ->     CONCAT(IF(@from like '%h%', '', '0h'), IF(@from like '%m%', '', '0m'), @from),
    ->     '%kh%im%ss')) as sec;
+------+
| sec  |
+------+
| 1734 |
+------+
1 row in set (0.00 sec)

mysql> set @from = '1h22m3s';
Query OK, 0 rows affected (0.00 sec)
mysql> select time_to_sec(str_to_date(
    ->     CONCAT(IF(@from like '%h%', '', '0h'), IF(@from like '%m%', '', '0m'), @from),
    ->     '%kh%im%ss')) as sec;
+------+
| sec  |
+------+
| 4923 |
+------+
1 row in set (0.00 sec)

mysql> set @from = '27s';
Query OK, 0 rows affected (0.00 sec)
mysql> select time_to_sec(str_to_date(
    ->     CONCAT(IF(@from like '%h%', '', '0h'), IF(@from like '%m%', '', '0m'), @from),
    ->     '%kh%im%ss')) as sec;
+------+
| sec  |
+------+
|   27 |
+------+
1 row in set (0.00 sec)

Base on the table structure provided, you may need to change type for column length from int(11) to varchar(20).

The new structure may be like this:

  `title` varchar(255) NOT NULL DEFAULT '',
  `embed` varchar(800) NOT NULL,
  `description` text NOT NULL,
  `keywords` varchar(255) NOT NULL,
  `length` varchar(20) NOT NULL DEFAULT '0',
  `source_thumb_url` varchar(255) NOT NULL,
  `record_num` int(11) NOT NULL AUTO_INCREMENT

Then after the import, use the following update to do the desired conversion.

update table_name 
set `length` = time_to_sec(str_to_date(
    CONCAT(IF(`length` like '%h%', '', '0h'), IF(`length` like '%m%', '', '0m'), `length`),
    '%kh%im%ss'));

Answered By – Dylan Su

Answer Checked By – Marilyn (BugsFixing Volunteer)