Query for duration between two times within 1 day

Question

Suppose I have a table that contain information on streaming media connections. In this table, I have a start time and end time for when the connection was initiated and then later closed.

Table: logs

• id (INT, PK, AUTO_INCREMENT)
• StartTime (DATETIME)
• EndTime (DATETIME)

I want to be able to run a query that will add up the total time connections were established for a day. This is obvious for connections within a day:

SELECT
    SUM(
        TIME_TO_SEC(
            TIMEDIFF(`EndTime`, `StartTime`)
        )
    )
WHERE (`StartTime` BETWEEN '2010-01-01' AND '2010-01-02);

However, suppose a StartTime begins one day, say around 11:00PM, and EndTime is some time the next day, maybe 3:00AM. In these situations, I want to allocate only the amount of time that occurred during the day, to that day. So, 1 hour would go towards the first day, and 3 hours would go to the next.

SUM(
    TIME_TO_SEC(
        TIMEDIFF(
            IF(`EndTime`>DATE_ADD('2010-01-01', INTERVAL 1 DAY), DATE_ADD('2010-01-01', INTERVAL 1 DAY), `EndTime`), 
            IF(`StartTime`<'2010-01-01', '2010-01-01', `StartTime`)
        )
    )/60/60
)

The thinking with this is that if the EndTime is more than the end of the day, then we'll just use the end of the day instead. If the StartTime is less than the beginning of the day, then we'll just use the beginning of the day instead.

So, I then need to wrap this all up into something that will generate a table that looks like this:

date, total
2010-01-01, 0
2010-01-02, 1.53
2010-01-03, 5.33

I thought this query would work:

SELECT 
`date`,
SUM(
    TIME_TO_SEC(
        TIMEDIFF(
            IF(`EndTime`>DATE_ADD(`date`, INTERVAL 1 DAY), DATE_ADD(`date`, INTERVAL 1 DAY), `EndTime`), 
            IF(`StartTime`<`date`, `date`, `StartTime`)
        )
    )/60/60
) AS `total_hours`
FROM 
(SELECT * FROM `logs` WHERE `StartTime` BETWEEN '2010-08-01' AND '2010-08-31') AS logs_small,
(SELECT DATE_ADD("2010-08-01", INTERVAL `number` DAY) AS `date` FROM `numbers` WHERE `number` BETWEEN 0 AND 30) AS `dates`
GROUP BY `date`;

Note the numbers table referenced is a table with just one column, number, with a series of integers, 0, 1, 2, 3, etc. I am using it here to generate a series of dates, which works fine.

The problem with this query is that I get inaccurate data. Specifically, rows in the logs table that have an EndDate that goes into the next day don't get any time counted in that next day. For example, if I had a row that started 2010-08-01 23:00:00 and ended 2010-08-02 01:00:00, then the resulting row for 2010-08-02 would add up to 0.

Is there a better way to do this? Ideally, I'd like to get 0 instead of null on days that don't have any records that match up to them as well.

Edit: To clarify, I want to turn this:

id, StartTime, EndTime
0, 2000-01-01 01:00:00, 2000-01-01 04:00:00
1, 2000-01-01 23:00:00, 2000-01-02 05:00:00
2, 2000-01-02 00:00:00, 2000-01-04 01:00:00

... into this:

date, total_hours
2000-01-01, 4
2000-01-02, 29
2000-01-03, 24
2000-01-04, 1
2000-01-05, 0

---

Solution

Thanks to jim31415 for coming up with the solution! I translated his answer over to the functions usable in MySQL and came up with this:

SELECT `d`.`Date`,
        SUM(COALESCE( 
        (CASE WHEN t.StartTime >= d.Date AND t.EndTime < DATE_ADD(d.Date, INTERVAL 1 DAY) THEN TIME_TO_SEC(TIMEDIFF(t.EndTime, t.StartTime))
              WHEN t.StartTime < d.Date AND t.EndTime <= DATE_ADD(d.Date, INTERVAL 1 DAY) THEN TIME_TO_SEC(TIMEDIFF(t.EndTime,d.Date))
              WHEN t.StartTime >= d.Date AND t.EndTime > DATE_ADD(d.Date, INTERVAL 1 DAY) THEN TIME_TO_SEC(TIMEDIFF(DATE_ADD(d.Date, INTERVAL 1 DAY),t.StartTime))
              WHEN t.StartTime < d.Date AND t.EndTime > DATE_ADD(d.Date, INTERVAL 1 DAY) THEN 24*60*60

         END), 0)
         )/60/60 ConnectionTime
FROM (SELECT DATE_ADD('2011-03-01', INTERVAL `number` DAY) AS `Date` FROM `numbers` WHERE `number` BETWEEN 0 AND 30) AS d
LEFT JOIN `logs` t ON (t.StartTime >= d.Date AND t.StartTime < DATE_ADD(d.Date, INTERVAL 1 DAY))
                        OR (t.EndTime >= d.Date AND t.EndTime < DATE_ADD(d.Date, INTERVAL 1 DAY))
                        OR (t.StartTime < d.Date AND t.EndTime > DATE_ADD(d.Date, INTERVAL 1 DAY))
GROUP BY d.Date
ORDER BY d.Date;

I should also note that the null values for EndTime weren't applicable in my situation, as I am reading from old log files in my application. If you need them though, Jim's post has them outlined quite well.

Answer 1

Use this

select startTime,duration  as duration,time,TIME_TO_SEC(TIMEDIFF(time,startTime))  as diff from <idling> limit 25;

select startTime,duration DIV 60  as duration,time,TIMESTAMPDIFF(MINUTE,startTime,time)  as diff from <idling> limit 25;

Answer 2

This is in MS SQL, but I think the logic applies and can be translated into MySQL. I wasn't sure how you wanted to handle EndTime that are null, so I commented that out.

select d.Date,
        sum(coalesce( 
        (case when t.StartTime >= d.Date and t.EndTime < dateadd(day,1,d.Date) then datediff(minute,t.StartTime,t.EndTime)
              when t.StartTime < d.Date and t.EndTime <= dateadd(day,1,d.Date) then datediff(minute,d.Date,t.EndTime)
              when t.StartTime >= d.Date and t.EndTime > dateadd(day,1,d.Date) then datediff(minute,t.StartTime,dateadd(day,1,d.Date))
              when t.StartTime < d.Date and t.EndTime > dateadd(day,1,d.Date) then 24*60
                --when t.StartTime >= d.Date and t.EndTime is null then datediff(minute,t.StartTime,getdate())
                --when t.StartTime < d.Date and t.EndTime is null then datediff(minute,d.Date,getdate())
         end), 0)
         ) ConnectionTime
from (select Date=dateadd(day, num, '2011-03-01') from #NUMBERS where num between 0 and 30) d
left join Logs t on (t.StartTime >= d.Date and t.StartTime < dateadd(day,1,d.Date))
                        or (t.EndTime >= d.Date and t.EndTime < dateadd(day,1,d.Date))
                        or (t.StartTime < d.Date and t.EndTime > dateadd(day,1,d.Date))
group by d.Date
order by d.Date

Answer 3

Use a union to make it easier for yourself

SELECT 
`date`,
SUM(
    TIME_TO_SEC(TIMEDIFF(`EndTime`,`StartTime`))/60/60
) AS `total_hours`
FROM 

(SELECT id, starttime, if (endtime > date then date else endtime) FROM `logs` WHERE `StartTime` >= date AND `StartTime` < date
union all
SELECT id, date, endtime FROM `logs` WHERE `enddate` >= date AND `enddate` < date and !(`StartTime` >= date AND `StartTime` < date)
union all
SELECT id, date, date_add(date, 1) FROM `logs` WHERE `enddate` > date AND `startdate` < date
) as datedetails inner join
  (SELECT DATE_ADD("2010-08-01", INTERVAL `number` DAY) AS `date` FROM `numbers` WHERE `number` BETWEEN 0 AND 30) AS `dates`
GROUP BY `date`;

Hope, I understood your question correctly

Edit: Forgot case when there is a multiday request that starts before the day asked for, and ended after