Days between two consecutive records in mysql

Question

I have these two rows:

id value created
1  10    2012-01-05
2  12    2012-02-13

How do i find the number of days in between these two consecutive records (based off of their created field)?

Thanks!

A bit more info needed, what would be the input and expected output? Two id's in, the date difference out, or just one id and get the diff between that and next or...? — Joachim Isaksson, Feb 19 '12 at 21:38
@JoachimIsaksson, and someone beat MarkBaker to it as well. :) possible duplicate of [MySql difference between two timestamps in days?](http://stackoverflow.com/questions/2546053/mysql-difference-between-two-timestamps-in-days) — bzlm, Feb 19 '12 at 21:43
There's very few questions asked here that haven't been answered time and again, guess I should have checked the "related" list of questions more closely — Mark Baker, Feb 19 '12 at 21:48

score 3 · Accepted Answer · answered Feb 19 '12 at 21:38

3

MySQL's DATEDIFF function should help

SELECT DATEDIFF(a.created,b.created)
  FROM table a,
       table b
 WHERE a.id = 1
   AND b.id = 2

answered Feb 19 '12 at 21:38

Mark Baker

Correct since the column is `DATE`, but just a note so no one does the same with `DATETIME` without reading your documentation link, `DATEDIFF` on `DATETIME`s from one second before midnight to midnight is 1 day since it only uses the `DATE` part. – Joachim Isaksson Feb 19 '12 at 21:48

1 Answers1