PostgreSQL Select Last 30 Days with Results

Question

I'm needing a PostgreSQL last 30 days report where some of the days might not have entries. So last Tuesday migth not have any records. Wednesday might have 8 records.

This is going into a bar chart so every day for the last 30 days is required. Relying on the database only, is there a way to force a result of the last 30 days, plus corresponding result values, even if nil? I'd have to set that last one to 0. Right now I have a result with only 16 days showing up.

I'm thinking I have to iterate the result in Ruby to get the data set I'm looking for. I'm trying to rely on the database instead of running outside for further processing.

SELECT DISTINCT creation_date::date as myday, count(id)
FROM queries
GROUP BY myday
ORDER BY myday DESC
LIMIT 30 

Answer 1

You might want to try to use generate_series to first get a counter from 0 to 29, and use a left outer join, try the following (note, it would be helpful to know what tables you are using):

SELECT a."TheDate", COALESCE(b."recs", 0)
FROM
(
SELECT CURRENT_DATE - i "TheDate"
    FROM generate_series(0, 29) i
) a
LEFT OUTER JOIN (
SELECT "TheDate", COUNT(*) "recs"
    FROM "Records"
    WHERE "TheDate" > (CURRENT_DATE - INTERVAL '30 DAY')::DATE
    GROUP BY "TheDate" 
)b ON a."TheDate"=b."TheDate"