Days diff based on selected date from succeeding row

Question

How can I get the succeeding or previous data row result from a query, as I wanted to compute days difference based on date_created for succeeding rows?

SELECT 
    -- *,
    h.date_created,
    -- (select date_created where id = h.id and pci_s = h.pci_s + 1) as dc,
    h.id,
    h.date_created,
    CONCAT('B', h.pci_b) AS batch,
    h.pci_s,
    DATEDIFF(h.date_created, h.date_created) as days_in_stage
FROM
    historical h
WHERE
    h.pci_b = 1
;

Expected

date_created    id  date_created    batch   pci_s   days_in_stage
2021-07-18T06:32:26Z    1   2021-07-18T06:32:26Z    B1  0   0
2021-07-20T06:32:26Z    4   2021-07-20T06:32:26Z    B1  1   2 

Here's the jsfiddle

http://sqlfiddle.com/#!9/f32a242/3

Currently using: Mysql 5.7.33

Answer 1

I get the succeeding or previous data row result

that you are looking for LEAD or LAG window function.

but your MySQL version is lower than 8.0 which didn't support window function.

you can try to use a subquery to make LEAD or LAG window function.

Query 1:

SELECT 
    h.date_created,
    h.id,
    h.date_created,
    CONCAT('B', h.pci_b) AS batch,
    h.pci_s,
    COALESCE(DATEDIFF(h.date_created,(
        SELECT hh.date_created
        FROM historical hh
        WHERE h.pci_b = hh.pci_b AND h.date_created > hh.date_created
        ORDER BY hh.date_created DESC
        LIMIT 1
    )),0) as days_in_stage
FROM
    historical h
WHERE
    h.pci_b = 1

Results:

|         date_created | id |         date_created | batch | pci_s | days_in_stage |
|----------------------|----|----------------------|-------|-------|---------------|
| 2021-07-18T06:32:26Z |  1 | 2021-07-18T06:32:26Z |    B1 |     0 |             0 |
| 2021-07-20T06:32:26Z |  4 | 2021-07-20T06:32:26Z |    B1 |     1 |             2 |