Generating series Postgres

Question

I want to be able to generate groups of row by days, weeks, month or depending on the interval I set

Following this solution, it works when granularity is by month. But trying the interval of 1 week, no records are being returned.

This is the rows on my table

This is the current query I have for per month interval, which works perfectly.

SELECT *
FROM  (
   SELECT  day::date
   FROM   generate_series(timestamp '2018-09-01'
                        , timestamp '2018-12-01'
                        , interval  '1 month') day
   ) d
LEFT   JOIN (
   SELECT date_trunc('month', created_date)::date AS day
        , SUM(escrow_amount) AS profit, sum(total_amount) as revenue
   FROM   (
   select distinct on (order_id) order_id, escrow_amount, total_amount, create_time from order_item
   WHERE  created_date >= date '2018-09-01'
   AND    created_date <= date '2018-12-01'
-- AND    ... more conditions

   ) t2 GROUP  BY 1
   ) t USING (day)
ORDER  BY day;

Result from this query

And this is the per week interval query. I will reduce the range to two months for brevity.

SELECT *
FROM  (
   SELECT  day::date
   FROM   generate_series(timestamp '2018-09-01'
                        , timestamp '2018-11-01'
                        , interval  '1 week') day
   ) d
LEFT   JOIN (
   SELECT date_trunc('week', created_date)::date AS day
        , SUM(escrow_amount) AS profit, sum(total_amount) as revenue
   FROM   (
   select distinct on (order_id) order_id, escrow_amount, total_amount, create_time from order_item
   WHERE  created_date >= date '2018-09-01'
   AND    created_date <= date '2018-11-01'
-- AND    ... more conditions

   ) t2 GROUP  BY 1
   ) t USING (day)
ORDER  BY day;

Take note that I have records from October, but the result here doesn't show anything for October dates.

Any idea what I am missing here?

Grzegorz Sancewicz · Accepted Answer · 2019-03-21T08:01:04.153

2

Results from your first query are not truncated to the begin of the week.

date_trunc('2018-09-01'::date, 'week')::date

is equal to

'2018-08-27'::date

so your join using day is not working

'2018-09-01'::date <> '2018-08-27'::date

Your query should look more like that:

SELECT *
FROM  (
   SELECT  day::date
   FROM   generate_series(date_trunc('week',timestamp '2018-09-01') --series begin trunc
                        , timestamp '2018-11-01'
                        , interval  '1 week') day
   ) d
LEFT   JOIN (
   SELECT date_trunc('week', created_date::date)::date AS day
        , SUM(escrow_amount) AS profit, sum(total_amount) as revenue
   FROM   (
   select distinct on (order_id) order_id, escrow_amount, total_amount, create_time from order_item
   WHERE  created_date::date >= date '2018-09-01'
   AND    created_date::date <= date '2018-11-01'
-- AND    ... more conditions

   ) t2 GROUP  BY 1
   ) t USING (day)
WHERE day >= '2018-09-01' --to skip days from begining of the week to the begining of the series before trunc
ORDER  BY day;

edited Mar 21 '19 at 08:01

answered Mar 21 '19 at 07:57

Grzegorz Sancewicz

106
1
4

sorry, modified the code. it should just be created_date without ::date – ellaRT Mar 21 '19 at 08:00
i've attached code sample. You should trunk dates in your series so they match week begining. 2018-09-01 is not the begining of the week so your series is not matching dates from aggregated sums – Grzegorz Sancewicz Mar 21 '19 at 08:03
I see! Let me try that! – ellaRT Mar 21 '19 at 08:03
huhu you save me day! It works! @Grzegorz Sancewicz – ellaRT Mar 21 '19 at 08:05

Generating series Postgres

1 Answers1