CODEWITHSUNDEEP
phpmysqltime
Joakim
Joakim

Reputation: 188

MySQL convert time minutes_seconds into seconds

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'

Upvotes: 1

Views: 562

Answers (2)

Strawberry
Strawberry

Reputation: 33935

Assuming length isn't really an integer...

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
  FROM my_table;

Upvotes: 0

Dylan Su
Dylan Su

Reputation: 6065

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'));

Upvotes: 3

Related Questions