How to Group by Week in Postgresql

How to group by week in postgresql

If you have multiple years, you should take the year into account as well. One way is:

SELECT date_part('year', author_date::date) as year,
       date_part('week', author_date::date) AS weekly,
       COUNT(author_email)           
FROM commits
GROUP BY year, weekly
ORDER BY year, weekly;

A more natural way to write this uses date_trunc():

SELECT date_trunc('week', author_date::date) AS weekly,
       COUNT(author_email)           
FROM commits
GROUP BY weekly
ORDER BY weekly;

How to group date by week in PostgreSQL?

We can do this using a calendar table. This answer assumes that a week begins with the first date in your data set. You could also do this assuming something else, e.g. a standard week according to something else.

WITH dates AS (
    SELECT date_trunc('day', dd)::date AS dt
    FROM generate_series
    ( '2018-11-09'::timestamp 
    , '2018-11-21'::timestamp
    , '1 day'::interval) dd
),
cte AS (
    SELECT t1.dt, t2.DATE_TIME, t2.SPEED,
        EXTRACT(week from t1.dt) week
    FROM dates t1
    LEFT JOIN yourTable t2
        ON t1.dt = t2.DATE_TIME::date
)

SELECT
    MIN(dt)::text || '-' || MAX(dt) AS DATE_TIME,
    AVG(SPEED) AS AVG_SPEED
FROM cte
GROUP BY
    week
ORDER BY
    MIN(dt);

Demo

Group weekly over several years (postgresql)

Use to_char() to format the year and week in a single sortable value:

SELECT to_char(date '2016-01-01', 'iyyy-iw'), SUM(qty)
FROM hr
WHERE date between '2016-01-01' and '2016-01-31'
GROUP BY to_char(date '2016-01-01', 'iyyy-iw')
ORDER BY 1

The format mask iyyy and iw use the ISO week numbering which you seem to be expecting (it e.g. assigns 2015-53 for 2016-01-01). If you don't want that you need to switch to yyyy and ww instead

How to do I sum these postgresql two tables and group by week and year?

You can join both tables, but as both tables could have different weeks, you need a FULL OUTER JOIN which will cost time.

If it is guaranteed that both SELECTs have the same weeks , a INNER JOIN will suffice.

CREATE tABLE waste("createdAt" timestamp  , "Quantity" integer )

CREATE tABLE sales_hourly("createdAt" timestamp  , "Quantity" integer)

SELECT t1.week_t1,COALESCE(sum_t1,0) sum_waste,t2.week_t2,  COALESCE(sum_t2,0) sum_slaes
FROM
(
    SELECT date_part('week', w."createdAt") week_t1,
        SUM("Quantity") sum_t1
    FROM waste as w
     WHERE date_trunc('year', w."createdAt"::timestamptz at time zone 'America/Denver') =
           date_trunc('year', current_date - interval '1 year')
    group by 1
) t1
    FULL OUTER JOIN 
(
    SELECT date_part('week', sh."createdAt") week_t2,
        SUM("Quantity") sum_t2
    FROM sales_hourly as sh
     WHERE date_trunc('year', sh."createdAt"::timestamptz at time zone 'America/Denver') =
           date_trunc('year', current_date - interval '1 year')
    group by 1) t2 ON t1.week_t1 = t2.week_t2

week_t1 | sum_waste | week_t2 | sum_slaes
:------ | --------: | :------ | --------:

db<>fiddle here

---

Related Topics