Determining the timespan to the previous record with the same ID

Question

I have a table with multiple occurrence of the same animal ID on different dates.

tableA
+-----------+------------+
| animalid  |  calvdate  |
+-----------+------------+
| A         | 2018-08-30 |
| B         | 2018-08-30 |
| A         | 2018-09-10 |
| C         | 2018-08-30 |
| A         | 2019-08-30 |
+-----------+------------+

I want to create a table that calculates number of days since the last recorded date of an animal

expected output
+-----------+------------+--------------+
|  animalid |  calvdate  | NumberOfDays |
+-----------+------------+--------------+
| A         | 2018-08-30 |            0 |
| A         | 2018-09-10 |           11 |
| A         | 2019-08-30 |          355 |
| B         | 2018-08-30 |            0 |
| C         | 2018-08-30 |            0 |
+-----------+------------+--------------+

I have tried using the following query:

SELECT
  animalid,
  calvdate, TO_DAYS(x.calvdate)-TO_DAYS(calvdate) AS NumberOfDays, 
  (SELECT calvdate FROM tableA as x WHERE animalid = animalid ORDER BY asc)
FROM
  tableA 

How can I generate my expected output?

Answer 1

This query will give you the results you want. It LEFT JOINs the original table to itself, where the calvdate in the second table is the maximum calvdate less than that in the first. Then we take the difference of those two dates to get the length of time, using COALESCE to deal with there being no matching row in the second table:

SELECT a.animalid, a.calvdate, 
       COALESCE(TIMESTAMPDIFF(DAY, b.calvdate, a.calvdate), 0) AS NumberOfDays
FROM tableA a
LEFT JOIN tableA b ON b.animalid = a.animalid AND
                      b.calvdate = (SELECT MAX(calvdate) 
                                    FROM tableA a2 
                                    WHERE a2.calvdate < a.calvdate 
                                      AND a2.animalid = a.animalid)
ORDER BY a.animalid

Output:

animalid    calvdate    NumberOfDays
A           2018-08-30  0
A           2018-09-10  11
A           2019-08-30  354
B           2018-08-30  0
C           2018-08-30  0

Demo on dbfiddle