mysql计算每天两个日期/时间之间的秒数 [英] mysql calculate seconds between two date/times for each day

问题描述

我有这张桌子，但是我无助于如何从这张桌子计算每一天的秒数:

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

现在我需要从2月到2月之间增加每天的秒数. 所以2月1日-x秒，2月2日-y秒，2月3日-0秒，等等.我知道该怎么做?非常感谢.

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.

推荐答案

使用示例:

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

这篇关于mysql计算每天两个日期/时间之间的秒数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！