SQL – Time difference between rows, conditional

I have a database for logging peeing/pooing time for a puppy. Each row contains a timestamp datetime, and bools pee and poo.

I’m trying to add a column for the time since last pee/poo. The verbose version of this column would be:

subtract this row’s datetime

from datetime of the row

with the highest datetime that is lower than this row’s datetime

and that has a poo value of 1

The basic query is:

SELECT 
    d.`datetime`,
    d.`poo`,
    d.`pee`/*,
    column with time since last poo as `poo_diff`,
    column with time since last pee as `pee_diff` */
FROM 
    `diary` d
WHERE 
    d.`user_id`=3
    AND 
    (d.`poo`=1 OR d.`pee`=1)
    AND 
    d.`datetime` >= DATE_ADD(CURDATE(), INTERVAL - 7 DAY)
    AND 
    d.`datetime` <= CURDATE();

I tried using LAG but it is conditional on finding the last row that had an applicable bool value. For example, if these are the results from a query:

…then LAG would not work for poo_diff as the difference should be from the row that most recently had a poo of 1.

How can I add this column?

Answer

You should be able to get the values you want using rolling variables:

SET @last_poo = '';
SET @last_pee = '';

SELECT 
    d.`datetime`,
    d.`pee`,
    SEC_TO_TIME(IF(@last_poo AND d.`poo`, TIMESTAMPDIFF(SECOND, @last_poo, d.`datetime`), 0)) AS `poo_diff`,
    SEC_TO_TIME(IF(@last_pee AND d.`pee`, TIMESTAMPDIFF(SECOND, @last_pee, d.`datetime`), 0)) AS `pee_diff`,
    IF(d.`poo` AND @last_poo:= d.`datetime`, d.`poo`, d.`poo`) AS `poo`,
    IF(d.`pee` AND @last_pee:= d.`datetime`, d.`pee`, d.`pee`) AS `pee`
FROM 
    `diary` d
WHERE 
    d.`user_id`=3
    AND 
    (d.`poo`=1 OR d.`pee`=1)
    AND 
    d.`datetime` >= DATE_ADD(CURDATE(), INTERVAL - 7 DAY)
    AND 
    d.`datetime` <= CURDATE()
ORDER BY d.`datetime` ASC;

in this case, @last_poo and @last_pee store the last datetime for their respective event, while iterating on the selected rows.

Note that this won’t work if you ORDER BY ... DESC, and I also had to swap some columns around, in order to have somewhere to update the variables after the difference is computed.

Finally, i’m using SEC_TO_TIME(... TIMESTAMPDIFF(...)) because otherwise it seemed to produce some weird values such as 03:00:00.00000