How to get date difference in SQL?-CodePudding

The original Table is like this :-

id  user_id item    created_at  revenue
1   109     milk    2020-03-03  123
2   139     biscuit 2020-03-18  421
3   120     milk    2020-03-18  176
4   108     banana  2020-03-18  862
5   130     milk    2020-03-28  333
6   103     bread   2020-03-29  862
7   122    banana   2020-03-07  952

Then I write a code like below:-

select 
    user_id,
    min(created_at) as first_date,
    max(created_at) as new_date
from amazon_transactions
group by user_id;

result:-

user_id first_date  new_date
117    2020-03-10   2020-03-22
113    2020-03-21   2020-03-21
125    2020-03-13   2020-03-13
120    2020-03-06   2020-03-27
107    2020-03-01   2020-03-01
128    2020-03-04   2020-03-28
142    2020-03-09   2020-03-09
110    2020-03-13   2020-03-27
145    2020-03-07   2020-03-07

How do I get date difference between the column of first_date & new_date?

CodePudding user response：

You can use DATEDIFF(interval, date1, date2) function in sql.

select 
    user_id,
    min(created_at) as first_date,
    max(created_at) as new_date,
    DATEDIFF(day, min(created_at), max(created_at)) as days_difference
from amazon_transactions
group by user_id;