MySQL 减去时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3543938/
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
subtracting timestamps
提问by Drew
I thought this would be a really simple thing, but I'm having a hard time finding any answers!
我认为这将是一件非常简单的事情，但我很难找到任何答案！
I have a support table, with added_onand closed_onfields that track when support tickets were opened and closed.
我有一个支撑台中，与added_on和closed_on该轨道时，支持票被打开和关闭的领域。
I'd like to run a query that (closed_on - added_on)to show the duration a ticket was open. Both fields are timestampformat. This is not as simple as I thought it would be...
我想运行一个查询(closed_on - added_on)以显示票证打开的持续时间。这两个字段都是timestamp格式。这并不像我想象的那么简单......
Ideally, final output would be X days Y hours Z minutesbut I'd be happy just to get total seconds or something, I can certainly take it from there.
理想情况下，最终输出将是X days Y hours Z minutes但我很高兴能得到总秒数或其他东西，我当然可以从那里得到它。
Sample data:
样本数据：
[id] [added_on] [closed_on]
1 2010-01-01 00:10:20 2010-01-02 00:10:20
1 2010-01-03 00:00:01 2010-01-03 13:30:01
采纳答案by OMG Ponies
Use:
用：
SELECT CONCAT(DATEDIFF(closed_on, added_on),
' days ',
SUBSTRING_INDEX(TIMEDIFF(closed_on, added_on), ':', 1),
' hours ',
SUBSTR(TIMEDIFF(closed_on, added_on), INSTR(TIMEDIFF(closed_on, added_on), ':')+1, 2),
' minutes')
With your example data, that gives me:
使用您的示例数据，这给了我：
0 days 00 hours 00 minutes
0 days 13 hours 30 minutes
回答by andrem
You can use TIMESTAMPDIFF to get the difference in seconds:
您可以使用 TIMESTAMPDIFF 获得以秒为单位的差异：
SELECT TIMESTAMPDIFF(SECOND,closed_on,added_on)...
SELECT TIMESTAMPDIFF(SECOND,closed_on,added_on)...
Edit: Another way would be to use UNIX_TIMESTAMP:
编辑：另一种方法是使用 UNIX_TIMESTAMP：
SELECT UNIX_TIMESTAMP(closed_on)-UNIX_TIMESTAMP(added_on)...
