0

I a have table in mysql with some data like this:

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

I need a Mysql query or a php code for finding difference between 'from' column and 'to' column of each rows, and find how many seconds are there in each day between 'from' and 'to' date separately, for example for row 1 the needed output be something like this:

difference between 2013-01-31 23:50:00 - 2013-02-02 09:00:00 for row 1

2013-01-31 : 600 sec (24:00:00 - 23:50:00 => 600 sec)

2013-02-01 : 86400 sec (24:00:00 - 00:00:00 => 86400 sec)

2013-02-02 : 32400 sec (09:00:00 - 00:00:00 => 32400 sec)

and so on ... for each row

MySQL command is preferred. which code can create this output? is there any specific function in php or mysql for creating this output?

I read these answers: mysql calculate seconds between two date/times for each day

MySQL: how to get the difference between two timestamps in seconds

but these are not my answer.

for information: link 1 is a question like my question but the answer is not true because "The command needs grouped by day" but how?! and the second is not my question but question one marked as duplicated with link 2 and it is not true.

Community
  • 1
  • 1
Ali
  • 602
  • 6
  • 18
  • Why solution @ first link you provided isn't a good one? What am I missing? – DonCallisto Aug 17 '15 at 10:31
  • @DonCallisto it just find the difference between to column and for example for row 1 gives: 119400 sec , but this is sum of them not each of them separately as I explained in the example. – Ali Aug 17 '15 at 10:40
  • Please try to explain better what you're trying to reach here: don't you need the difference (in seconds) between from and to for each record? – DonCallisto Aug 17 '15 at 10:47
  • @DonCallisto I need the difference but difference for example for row one (119400 sec) separated to days between days among 'from' and 'to' column. – Ali Aug 17 '15 at 10:51

1 Answers1

1

Let me assume that you have a table of dates. If so, you can use a join or correlated subquery:

select d.date,
       (select sum(timestampdiff(second,
                                 greatest(d.date, t.from),
                                 least(d.date, t.to)
                                ))
        from table t
        where t.to >= d.date and
              t.from < date_add(d.date, interval 1 day)
       ) as NumSeconds
from dates d;

If you don't have a dates table of some sort, you can create one on the fly:

from (select date('2013-01-31') as date union all
      select date('2013-02-01') union all
      . . .
     ) dates
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • thanks for answer, can you explain the answer exactly with my columns of my table, the information is => table name: hist , columns: ID,From_Time,To_Time . – Ali Aug 17 '15 at 10:58
  • @Ali . . . This answer uses the columns that you specify in the question. That is the proper place to put the correct format of the data. – Gordon Linoff Aug 17 '15 at 12:21