I have table in mysql which contain start_date, end_date and id like this
start_date end_date id
---------------------------------------------
2018-01-01 2018-01-01 5
2018-01-03 2018-01-03 5
2018-01-03 2018-01-08 5
2018-01-06 2018-01-07 7
2018-01-07 2018-01-07 7
2018-01-09 2018-01-11 7
2018-01-02 2018-01-02 8
2018-01-02 2018-01-04 8
2018-01-08 2018-01-08 9
I want output in mysql for total number of days for id like.
total_days id
-------------------------
7 5
5 7
3 8
1 9
There is day gap between dates of particular ids. Please check start and end dates of each id. I think it need to put all dates in one sequence and then calculate days from first to next. I don't know how to do it. 2018-01-08 to 2018-01-08 should be one day. 2018-01-08 to 2018-01-09 should be 2 days but not 1. Please help. Program is developing in php..
I found the solution that it is not possible in hardcore mysql. I need to do using programming language. As I developed my software using PHP, So I calculated days using PHP code. I align-sort all dates one by other and then calculate days between each date. Thanks for all who gave answers to my question.
You are looking for the DATEDIFF function:
MS SQL SERVER:
SELECT id, sum(DATEDIFF(day, startdate, enddate) +1) FROM table group by id;
MYSQL:
SELECT id, DATEDIFF(max(enddate), min(startdate)) + 1 FROM demo group by id;
Demo Fiddle: DEMO
Datediff documentation MS SQL Server : MS SQL SERVER Datediff
Datediff documentation MYSQL : MYSQL Datediff
DateDiff function supports minus and plus in output:
SELECT DATEDIFF(start_date, end_date) + 1
select distinct id,count(start_date-end_date) as "total_days" from table_name