34

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.

peter
  • 4,289
  • 12
  • 44
  • 67
  • @dom: The OP wants the results grouped by day. – eggyal Mar 01 '13 at 11:54
  • For an SQL query to return the results you want, you must join your table with a column that contains the dates for which you wish to have results. MySQL does not natively provide any means of generating all dates (or even integers) within a given interval, so you must either materialise such a table in your application layer, or else store a program within MySQL that generates such a temporary table. If doing the latter, you may as well store a program within MySQL that builds your desired resultset more directly. This all suggests that MySQL may not be the best tool for this problem. – eggyal Mar 01 '13 at 12:03
  • Slight variation to give number of sections per fromDate: select fromDate, sum(seconds) from ( SELECT id, TIMESTAMPDIFF(SECOND, `from`, `to`) as seconds, date(`from`) as fromDate from t1 ) t2 group by fromDate; http://rextester.com/QEPL38222 – Sam Sippe Feb 22 '17 at 21:10

1 Answers1

77

Use TIMESTAMPDIFF in MySQL:

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
ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Dipesh Parmar
  • 27,090
  • 8
  • 61
  • 90