SQL 将 datediff 显示为秒、毫秒
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15660647/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Show datediff as seconds, milliseconds
提问by Pieter_Daems
I'm trying to calculate the difference between two datetime values.
我正在尝试计算两个日期时间值之间的差异。
I tried datediff(s, begin,end)and datediff(ms, begin,end)however I want the difference to be returned as seconds,millisecondslike the following:
我尝试过datediff(s, begin,end)，datediff(ms, begin,end)但是我希望将差异返回seconds,milliseconds如下：
4,14
63,54
回答by Aaron Bertrand
SELECT
DATEDIFF(MILLISECOND, begin, end) / 1000,
DATEDIFF(MILLISECOND, begin, end) % 1000
FROM ...;
If you absolutely must form it as a string in your SQL query (can't your presentation tier do that?), then:
如果您绝对必须在 SQL 查询中将其形成为字符串（您的表示层不能这样做吗？），那么：
SELECT
CONVERT(VARCHAR(12), DATEDIFF(MILLISECOND, begin, end) / 1000)
+ ','
+ RIGHT('000' + CONVERT(VARCHAR(4), DATEDIFF(MILLISECOND, begin, end) % 1000), 3)
FROM ...;
Also I really hope you have better column names than beginand end.
另外我真的希望你有比beginand更好的列名end。
回答by Wolfgang
Actually the marked answer will produce wrong results for milliseconds 1 - 99:
实际上，标记的答案会在 1 - 99 毫秒内产生错误的结果：
Example 1 second, 27 milliseconds:
1) DATEDIFF % 1000 will return 27
2) CONVERT will convert to '27'
3) String concatenation will build '1' + ',' + '27'
4) Result: '1.27' which means 270ms rather than 27ms
Don't forget to pad the milliseconds to three zeros:
不要忘记将毫秒填充为三个零：
DECLARE @start datetime2(7) = '2015-07-03 09:24:33.000'
DECLARE @end datetime2(7) = '2015-07-03 09:24:34.027'
SELECT
CAST (DATEDIFF(SECOND, @start, @end) AS nvarchar(3)) + N'.' +
RIGHT('000' + CAST((DATEDIFF(MILLISECOND, @start, @end) % 1000) AS nvarchar(3)), 3)
回答by Павел П
DATEDIFF takes only two arguments in MySQL. This works for me:
DATEDIFF 在 MySQL 中只接受两个参数。这对我有用：
TIMESTAMPDIFF(SECOND, NOW(), '2019-09-09 18:52:00')
