Count days for each month between two dates - postgresql

user_6143299 0
Viewed 549

I am trying to write a query which gives the number of days in each month between two specified dates.

Example:

date 1: 2018-01-01

date 2: 2018-05-23

Expected Output:

month      days

2018-01-01, 31

2018-02-01, 28

2018-03-01, 31

2018-04-01, 30

2018-05-01, 23

postgresql
1 Answers

Use generate_series and group by date_trunc

SELECT date_trunc('month',dt) AS month,
       COUNT(*) as days
FROM generate_series( DATE '2018-01-01',DATE '2018-05-23',interval '1 DAY' )
as dt group by date_trunc('month',dt) 
order by month;

Demo

user_79985910
Related