How do I use Datediff between multiple dates in MySQL?

Question

I want to find out how often my customers place an order within 7 days after their last order. I know how i can select customer ids, with the dates of their purchases, but how do i select the differences between these multiple dates? datediff probably, but how?

I am looking for a result like this:

customer_id,  date1 | date2 | date3, diff date3-date2 | diff date2-date1

This is what I have now:

SELECT c.id,GROUP_CONCAT(DATE(co.date_order))
FROM customer c
JOIN customer_order co ON co.id_customer=c.id
GROUP BY c.id

possible duplicate of http://stackoverflow.com/questions/2490173/how-to-get-the-number-of-days-of-difference-between-two-dates-on-mysql — Harry, Oct 20 '14 at 14:52
Basically you need to use a query variable to keep track of the date from the "previous" record, e.g `select @prev := min(order_date) from ...` to initialize, then `select datediff(order_date, @prev), @prev := order_date` to derive a field with the difference. — Marc B, Oct 20 '14 at 14:54

score 0 · Answer 1 · answered Oct 20 '14 at 16:48

Below is the syntax for DateDiff function:

DATEDIFF(dateFormat,startdate,enddate)

Where dateFormat is either day,month or year based on your requirement. Modify your query and pass your values inside the function and you can acheive your result. Hope this helps.

How do I use Datediff between multiple dates in MySQL?

1 Answers1