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-21 08:01:04Z

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, 2019 at 8:01

answered Mar 21, 2019 at 7:57

Grzegorz Sancewicz

1061 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ellaRT Over a year ago

sorry, modified the code. it should just be created_date without ::date

Grzegorz Sancewicz Over a year ago

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

ellaRT Over a year ago

I see! Let me try that!

ellaRT Over a year ago

huhu you save me day! It works! @Grzegorz Sancewicz

Collectives™ on Stack Overflow

Generating series Postgres

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related