SQL - Time difference between rows, conditional

Question

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?

forpas · Answer

You can do it with window function MAX():
select *,
  case when poo then sec_to_time(
    timestampdiff(
      second, 
      max(case when poo then datetime end) over (rows between unbounded preceding and 1 preceding),
      datetime
    )
  ) end poo_diff,
  case when pee then sec_to_time(
    timestampdiff(
      second, 
      max(case when pee then datetime end) over (rows between unbounded preceding and 1 preceding),
      datetime
    )
  ) end pee_diff
from diary
order by datetime

See the demo.

Pepper · 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

SQL - Time difference between rows, conditional

2 Answers

Add your own answers!

Ask a Question