CODEWITHSUNDEEP
postgresql
ellaRT
ellaRT

Reputation: 1366

Generating series Postgres

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

enter image description here

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

enter image description here

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.

enter image description here

Any idea what I am missing here?

Upvotes: 1

Views: 83

Answers (1)

Grzegorz Sancewicz
Grzegorz Sancewicz

Reputation: 106

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;

Upvotes: 2

Related Questions