How to combine several time spans

Question

Hello,

Lets say, i have the following table

| start               | end                 | activity
+---------------------+---------------------+---------
| 2010-07-26 09:10:00 | 2010-07-26 09:21:00 | 6
| 2010-07-26 09:35:00 | 2010-07-26 09:47:00 | 5
| 2010-07-26 10:05:00 | 2010-07-26 10:45:00 | 5
| 2010-07-26 10:50:00 | 2010-07-26 11:22:00 | 6
| 2010-07-26 13:15:00 | 2010-07-26 13:43:00 | 7
| 2010-07-26 14:12:00 | 2010-07-26 14:55:00 | 2

I want to combine the small time spans, getting the average minutes with activity per hour. Something like that:

| start               | minutes_activity    | avg_activity
+---------------------+---------------------+---------
| 2010-07-26 09:00:00 | 42                  | {avg value}
| 2010-07-26 10:00:00 | 50                  | {avg value}
| 2010-07-26 11:00:00 | 22                  | {avg value}
| 2010-07-26 13:00:00 | 28                  | {avg value}
| 2010-07-26 14:00:00 | 43                  | {avg value}

Note that some records can have activity minutes in two hours, i.e 10:50:00 - 11:22:00. In this case, 10 minutes should be added to 10:00 and 22 minutes to 11:00.

A solution, be it MySql, PHP or both is appreciated.

Thank you advance.

Answer 1

Hi,

I'll outline a solution for you (Brute Force):

1. Get all your data from the database to PHP (as is)
2. Get an empty arrays for one hour intervals
3. Loop all your data and get the timestamp where the hour for start started (i.e. round down start); get the number of hours between that starting reference point computed and your start and end values (as floats)
4. With this data extracted it should be fairly easy (a few ifs and a while) to separate you activity to hours and add them to the array from 2.
5. At this point you will have all your data assigned grouped by one hour intervals. Proceed with needed processing.

I recommend strtotime when computing timestamps because you have standard time formats. For adding one hour to your time you may use strtotime('+1 hour', $timestamp).

Answer 2

Let's assume that your table is named records and that your time intervals don't span through more than two hours:

SELECT DISTINCT(CONCAT(date,' ',IF(LENGTH(hour)=1, CONCAT('0',hour), hour), ':00:00')) AS start,
       SUM(minutes) AS minutes_activity,
       FORMAT(avg(activity),0) AS avg_activity
FROM (
      SELECT date, hour, minutes, activity
      FROM (
            SELECT DATE(start) AS date,
                   HOUR(ADDDATE(start, INTERVAL 1 HOUR)) AS hour,
                   IF(HOUR(start) < HOUR(end), MINUTE(end), 0) AS minutes,
                   activity AS activity
            FROM records
            HAVING minutes>0
           ) t1
      UNION (
             SELECT DATE(ADDDATE(start, INTERVAL 1 DAY)) AS date,
                    HOUR(ADDDATE(start, INTERVAL 1 HOUR)) AS hour,
                    IF(HOUR(start) > HOUR(end), MINUTE(end), 0) AS minutes,
                    activity AS activity
             FROM records
             HAVING minutes>0
                    AND hour=0
            )
      UNION (
             SELECT DATE(start) AS date,
                    HOUR(start) AS hour,
                    IF(HOUR(start) < HOUR(end),
                       (60 - MINUTE(start)),
                       IF(
                          DATE(start) < DATE(end),
                          (60 - MINUTE(start)),
                          (MINUTE(end) - MINUTE(start))
                       )
                    ) AS minutes,
                    activity AS activity
             FROM records
            )
      ORDER BY date ASC, hour ASC) t2
GROUP BY CONCAT(date,' ',IF(LENGTH(hour)=1, CONCAT('0',hour), hour), ':00:00');

... however, if you have an interval that goes from e.g. 2010-10-08 01:30:00 to 2010-10-08 03:13:00, a (not so) simple SQL query won't do it.