Calculate duration in days between start and end date for each year

Question

I'm trying to calculate the duration in days for different years where:

startdate     enddate       duration (days)
2016-09-20    2018-09-20    730

Where i want the following outcome in one row as duration per year, with the year as column name and the days as result:

2016 = 103 days - 2017 = 365 days - 2018 = 263 days

I couldn't find a specific solution except using DATEDIFF and entering the enddate of each year but this doesn't calculate the remaining days over next years. Anyone who can help me out finding a solution with MySQL?

Answer 1

Assuming (hoping) that you have a numbers table you can the following calculation:

SELECT startdate,
       enddate,
       number AS year_num,
       DATEDIFF(
              LEAST(DATE(CONCAT(number, '-12-31')), enddate),
           GREATEST(DATE(CONCAT(number, '-01-01')), startdate)
       ) + 1 AS num_days
FROM t
INNER JOIN (
    SELECT 2010 AS number UNION ALL
    SELECT 2011 UNION ALL
    SELECT 2012 UNION ALL
    SELECT 2013 UNION ALL
    SELECT 2014 UNION ALL
    SELECT 2015 UNION ALL
    SELECT 2016 UNION ALL
    SELECT 2017 UNION ALL
    SELECT 2018 UNION ALL
    SELECT 2019
) AS numbers ON number BETWEEN YEAR(startdate) AND YEAR(enddate)