How to display empty rows in this specific case?

Question

I'm trying to build a query using the various examples I found here but with little success so far:

SELECT count(o.hist_create) AS daily_count, d.day AS date_column 
FROM  (
SELECT day::date FROM generate_series(
(SELECT hist_create FROM orders ORDER BY hist_create LIMIT 1),
CURRENT_TIMESTAMP, 
interval '1 day')day)d 
LEFT JOIN orders o ON date_trunc('day', o.hist_create) = d.day
WHERE o.customerId = '1234'
GROUP  BY d.day 
ORDER  BY d.day;

So here I want to count orders on a day to day basis from the first order's timestamp to today for a specific customer, and while this works, it doesn't fetch the dates when there was no order for this customer. I would like to see something like:

number | date 
     15| 06-12-2017 
     0 | 07-12-2017
     9 | 08-12-2017
     11| 09-12-2017

But I get:

number | date 
     15| 06-12-2017 
     9 | 08-12-2017
     11| 09-12-2017

I thought the join would take care of that and it seems this solution worked for others, maybe not when targeting a customer the way I do, though?

The data type of hist_create is timestamp.

I think you should show us some data. – Tim Biegeleisen Jan 06 '18 at 11:00 — Tim Biegeleisen, Jan 06 '18 at 11:00

score 1 · Accepted Answer · answered Jan 06 '18 at 11:01

The issue is that you're filtering on a field from the orders table, which defeats the purpose of using an outer join. Include that condition in your join instead:

SELECT count(o.hist_create) AS daily_count, d.day AS date_column 
FROM  (
SELECT day::date FROM generate_series(
(SELECT hist_create FROM orders ORDER BY hist_create LIMIT 1),
CURRENT_TIMESTAMP, 
interval '1 day')day)d 
LEFT JOIN orders o ON date_trunc('day', o.hist_create) = d.day AND o.customerId = '1234'

GROUP  BY d.day 
ORDER  BY d.day;

Thanks a lot, I can't believe I didn't see that, I'll wear a dunce cap for the rest of the day. — fen, Jan 06 '18 at 11:04

score 0 · Answer 2 · answered Jan 06 '18 at 22:14

@fauxmosapien pointed out your main issue with the LEFT JOIN.

But since your column hist_create has data type timestamp, the query can be further improved like this:

SELECT count(o.hist_create) AS daily_count, d.day::date AS date_column 
FROM  (
   SELECT generate_series(min(hist_create), now()::timestamp, interval '1 day') AS day
   FROM   orders
   ) d
LEFT   JOIN orders o ON date_trunc('day', o.hist_create) = d.day
                    AND o.customerId = '1234'  -- why is the number quoted?
GROUP  BY d.day 
ORDER  BY d.day;

Your original query generates a series of timestamptz values (because that's what CURRENT_TIMESTAMP returns) casts to date before it has to cast back to timestamp for the match to hist_create in the LEFT JOIN.

Instead, create a timestamp series and match directly. Only cast to date for output (or format the value any way you like for display with to_char()). Test with EXPLAIN ANALYZE to verify that it performs slightly better. It also minimizes sneaky corner case errors with casting between timestamptz and date / timestamp. More details:

Generating time series between two dates in PostgreSQL

How to display empty rows in this specific case?

2 Answers2