How do you select data from PostgreSQL database, but if no data is present for a given day, then return 0?

Question

I have the following query:

SELECT created_at::DATE, count (*) 
FROM messages      
WHERE city = 'los angeles'     
GROUP BY created_at::DATE

Which works great. The challenge is that if there are no messages for a given date, then it returns no record for that date. How do you make the above query return the date and 0 if there are no messages on that date, for all days between a given date and today?

Working in PostgreSQL 8.3.

Thanks!

Answer 1

It sounds like you need a table of all the dates you are interested in, as it may contain dates not in your messages table. If you have, or build, this table then left join with the messages table and do count on a column that table--it will return 0 where nothing matches the join.

select d.created_at, count(m.messageId)
from possibleDates d
left join messages m
    on d.created_at = m.created_at
group by d.created_at