mysql - Mysql 时差不起作用 MySQL 客户端版本：3.23.49

Question

TIMEDIFF 函数不起作用我想获取列类型 = 1 到列类型 = 1 和 old_value = 10 我的 2 个查询的时间差，如下所示。

SELECT date_modified
FROM mantis_bug_history_table
WHERE bug_id =75616
AND TYPE =0
AND old_value =10
ORDER BY date_modified DESC 

output
------
2019-10-28 10:26:57

SELECT date_modified
FROM mantis_bug_history_table
WHERE bug_id =75616
AND TYPE =1
ORDER BY date_modified DESC

output
------
2019-10-28 10:15:04

[在此处输入图像描述][1] 错误 SQL 查询：文档

SELECT TIMEDIFF(
SELECT date_modified
FROM mantis_bug_history_table
WHERE bug_id =75616
AND TYPE =1
ORDER BY date_modified DESC ,
SELECT date_modified
FROM mantis_bug_history_table
WHERE bug_id =75616
AND TYPE =0
AND old_value =10
ORDER BY date_modified DESC )

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT date_modified
FROM mantis_bug_history_table
WHERE bu

上面的查询试图获取时间差异

标签： mysqltimedifference

Answer 1

(...subquery)在作为参数传递给之前在子查询周围添加括号TIMEDIFF：

(SELECT date_modified FROM mantis_bug_history_table WHERE bug_id =75616 AND TYPE =0 AND old_value =10 ORDER BY date_modified DESC )

(SELECT date_modified FROM mantis_bug_history_table WHERE bug_id =75616 AND TYPE =1 ORDER BY date_modified DESC)

最终查询：

 SELECT TIMEDIFF((SELECT date_modified FROM mantis_bug_history_table WHERE bug_id =75616 AND TYPE =0 AND old_value =10 ORDER BY date_modified DESC ),
 (SELECT date_modified FROM mantis_bug_history_table WHERE bug_id =75616 AND TYPE =1 ORDER BY date_modified DESC));

请注意： TIMEDIFF 函数期望每个子查询只返回一个 row+col，如果将来可能有多个，请考虑将LIMIT 1与它们都添加。