mysql - 两次之间的差异存储为文本

Question

我有两个字段 - TimeStart 和 TimeFinish - 在 sql 数据库 中存储为 text。我正在尝试计算两个字段之间的秒数差异，但在计算的 Difference 字段中我得到的只是 NULL。有什么想法吗？

SELECT TimeStart
    , TimeFinish
    , TIMESTAMPDIFF(SECOND
                   , STR_TO_DATE(TimeFinish, '%h:%i')
                   , STR_TO_DATE(TimeStart, '%h:%i')) AS Difference
FROM table

输出示例

为了尝试找出答案，我刚刚使用以下方法完成了 STR_TO_DATE:

SELECT TimeStart
    , TimeFinish 
    , (STR_TO_DATE(TimeFinish, '%H:%i:%s')) AS Finish
    , (STR_TO_DATE(TimeStart, '%H:%i')) AS START 
FROM table

得到这个:

所以有些东西在工作。

Answer 1

您的方法的第一个问题是“%h”是 12 小时制的小时，对于 24 小时制您需要使用“%H”:

mysql> SELECT STR_TO_DATE(TimeFinish, "%h"), STR_TO_DATE(TimeFinish, "%H") from table;
+-------------------------------+-------------------------------+
| STR_TO_DATE(TimeFinish, "%h") | STR_TO_DATE(TimeFinish, "%H") |
+-------------------------------+-------------------------------+
| NULL                          | 21:00:00                      |
+-------------------------------+-------------------------------+
1 row in set, 2 warnings (0.00 sec)

尽管它本身没有帮助，TIME_DIFFERENCE 仍然返回 NULL。要解决这个问题，您可以将其替换为 TIME_TO_SEC 并计算它们的差异:

mysql> select TimeStart, TimeFinish, TIME_TO_SEC(STR_TO_DATE(TimeFinish, '%H:%i')) - TIME_TO_SEC(STR_TO_DATE(TimeStart, '%H:%i')) as Difference from moo;
+-----------+------------+------------+
| TimeStart | TimeFinish | Difference |
+-----------+------------+------------+
| 21:45     | 21:47      |        120 |
+-----------+------------+------------+
1 row in set (0.00 sec)