MySQL mysql计算每天两个日期/时间之间的秒数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15157090/
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
mysql calculate seconds between two date/times for each day
提问by peter
I have this table but I am helpless how to calculate seconds for each separate day from this table:
我有这张表，但我无能为力如何从这张表中计算每一天的秒数：
id from to
---------------------------------------------------
1 2013-01-31 23:50:00 2013-02-02 09:00:00
2 2013-02-05 11:21:12 2013-02-08 01:01:01
3 2013-02-08 17:33:44 2013-02-08 18:22:55
4 2013-02-12 01:40:12 2013-02-12 02:00:59
5 2013-02-28 01:40:12 2013-03-02 02:00:59
Now I need to gain number of seconds for each day in february between from and to. so for 1st Feb - x seconds, 2nd Feb - y seconds, 3rd Feb - 0 seconds, etc. Any idea how should I do it? Many thanks.
现在我需要在 2 月之间的每一天获得秒数。所以对于 2 月 1 日 - x 秒、2 月 2 日 - y 秒、2 月 3 日 - 0 秒等。知道我该怎么做吗？非常感谢。
回答by Dipesh Parmar
Use TIMESTAMPDIFFin MySQL:
在 MySQL 中使用TIMESTAMPDIFF：
SELECT TIMESTAMPDIFF(SECOND,from,to);
Example:
例子：
SELECT TIMESTAMPDIFF(SECOND,'2016-01-01 00:00:00','2016-01-11 00:00:00'); -- 864000
SELECT TIMESTAMPDIFF(MINUTE,'2016-01-01 00:00:00','2016-01-11 00:00:00'); -- 14400
SELECT TIMESTAMPDIFF(HOUR,'2016-01-01 00:00:00','2016-01-11 00:00:00'); -- 240
SELECT TIMESTAMPDIFF(DAY,'2016-01-01 00:00:00','2016-01-11 00:00:00'); -- 10
