CODEWITHSUNDEEP
mysql
Fernando
Fernando

Reputation: 4629

Mysql SUM time with ranges in different days

I have the following table structure ( reduced of course ):

CREATE TABLE `log` (
  `ID` int(11) unsigned NOT NULL auto_increment,
  `StartTime` datetime default NULL,
  `FinishTime` datetime default NULL,
  PRIMARY KEY  (`ID`),
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

Some sample data:

StartTime           FinishTime
2011-06-02 18:00:00 2011-06-02 22:59:59
2011-06-02 23:00:00 2011-06-03 04:00:00
2011-06-03 10:00:00 2011-06-03 12:00:00

Is it possible to use Mysql to SUM the total time on 2011-06-03 considering the second row, which starts yesterday but ends today?

My current query:

SELECT SUM(UNIX_TIMESTAMP(FinishTime) - UNIX_TIMESTAMP(StartTime)) AS seconds
FROM log WHERE StartTime >= '2011-06-03 00:00:00' AND FinishTime  <= '2011-06-03 23:59:59'

This removes the second record because of the StartTime condition.

What should i do to get the total of 6 hours used today? ( second + third row, except the hours logged yesterday for second row ).

Thanks!

Upvotes: 1

Views: 454

Answers (1)

Dan J
Dan J

Reputation: 16728

What you can do is look for Start/Finish times that contain some or all of the target day, but "crop" the times to the limits of that day. For example:

SELECT SUM(
  UNIX_TIMESTAMP(
    CASE WHEN FinishTime > '2011-06-03 23:59:59' THEN '2011-06-03 23:59:59' 
         ELSE FinishTime END
  ) - 
  UNIX_TIMESTAMP(
    CASE WHEN StartTime < '2011-06-03 00:00:00' THEN '2011-06-03 00:00:00' 
         ELSE StartTime END
  )
) AS seconds
FROM log 
WHERE FinishTime >= '2011-06-03 00:00:00' AND StartTime  <= '2011-06-03 23:59:59'
AND FinishTime > StartTime

N.B.: Note that extra criterion at the end, there. You want all records that finish within or after the target day, and all records that start before or within the target day, but because of the possible overlap implied you also need to specify that StartTime comes before FinishTime!

Upvotes: 1

Related Questions