CODEWITHSUNDEEP
mysqltime
grunk
grunk

Reputation: 14938

Timediff avoid negative value

I have the following table : 

CREATE TABLE `activities` (
  `id` int(10) unsigned NOT NULL,
  `startdate` date NOT NULL,
  `nightstart` time DEFAULT NULL COMMENT 'Heure de début du travail de nuit',
  `nightend` time DEFAULT NULL COMMENT 'Heure de fin du travail de nuit',
)

in which datas could look something like : 

> +---------------------------------------------------------------+
> +   id   |    startdate    |    nightstart    |     nightend    |
> +---------------------------------------------------------------+
> +    1   |    2016-09-15   |      21:00:00    |     03:00:00    +
> +    2   |    2016-09-25   |      01:00:00    |     04:30:00    +
> +    3   |    2016-10-01   |      00:00:00    |     03:35:00    +

My goal is to get the amount of time spend at night each month. The nighstart value can start between 21:00:00 et 04:59:59 wherease the nightend value can range between 21:00:01 and 05:00:00

I have tried something like this : 

SELECT MONTH(startdate) as month,
       SUM(TIME_TO_SEC(TIMEDIFF(nightend,nightstart))) as total  
FROM `activities` 
WHERE nightstart IS NOT NULL AND YEAR(startdate) = 2016 
GROUP BY month ORDER BY month ASC

which doesnt works when i have time overlaping on 2 days (ie : starting at 21:00:00 ending at 01:00:00) because 01 -21 will give me a negative value. I only have a start date and that scheme can't be changed.

I could solve the problem by getting all the data on the PHP side and doing some calculation for each line : 

if ($nightstart < $nightend) {
    $totalSec = $rawData['total'];
} else {

    if ($nightstart >= 21 && ($nightend <= 4 || ($nightend == 5 && $minEnd == 0))) {
        $nightend->add(new \DateInterval('P1D'));
        $totalSec = $nightend->getTimestamp() - $nightstart->getTimestamp();
    }
}

but i'd rather do it in SQL if it's possible.

Is there a way to tell SQL that the end time is from the next day if the start time is before midnight ?

Upvotes: 0

Views: 838

Answers (1)

Shadow
Shadow

Reputation: 34285

If the nightend value falls between 00:00:00 and 05:00:00 (inclusive), then you need to add 1 day to the nightend value. You can do this with MySQL's if() and addtime() functions:

TIMEDIFF(if(nightend >='00:00:00' and nightend<='05:00:00',addtime(nightend,'24:00:00'),nightend),nightstart)

Upvotes: 1

Related Questions