php - MySQL 将时间 minutes_seconds 转换为秒

Question

各位 overflow 用户，您好，我一直在寻找如何使用 MySQL 命令将给定的时间转换为 HHhMMmssS ex:28m54s 或 1h22m3s 或 27s 为纯秒数。

输出应该以纯秒为单位，例如:

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

这些值已导入到名为长度的列中。

如果无法做到这一点，第二好的选择是 PHP。这是一份工作，所以复制粘贴答案就可以了:)

MariaDB结构

  `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)

选择字符串

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;

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

返回:错误代码:1064您的 SQL 语法有误；查看与您的 MariaDB 服务器版本对应的手册，了解在 'select time_to_sec(str_to_date( CONCAT(IF(length like '%h%', '', '0h'), I' at line 1

并对表格长度进行了修改

+------------------+--------------+------+-----+---------+----------------+
| 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)

更新草莓的回答

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;

错误代码:1054“字段列表”中的未知列“长度”

Answer 1

利用 time_to_sec 和 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;

演示:

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)

根据提供的表结构，您可能需要将 length 列的类型从 int(11) 更改为 varchar(20) .

新的结构可能是这样的:

  `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

然后在导入之后，使用下面的update 进行所需的转换。

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