Multi tool use

how to fetch date wise data between two dates in PostgreSQL?

I have two dates and i want to fetch the data between the dates date wise and if a date has no entry in db so it is comes with the zero count, i have mytable in which id, createddate columns are there.

zero

id, createddate

I have tried like:

select COALESCE(to_char(createddate, 'YYYY-MM-DD'), '0') as date,
count(id) as totalno
from mytable
where (createddate >= '2017-07-13 00:00:00'
and createddate <= '2018-07-13 23:59:59')
group by to_char(createddate, 'YYYY-MM-DD'),
date_part('day', createddate)


This query does not return me the dates data with 0 totalno which has no entry in db.

0

As you can we the dates 15,16,18,22,23 did not come with the 0 count.
please help me out.

15,16,18,22,23

0

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.