CODEWITHSUNDEEP
sqlpostgresql
Matias
Matias

Reputation: 332

Fetch records from current week only

I made a discord bot where I record contributions/posts of members in a database, in the following table.

CREATE TABLE IF NOT EXISTS posts (
    id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    ...
    post_date timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);

At the end of each week I'd manually run a command to fetch the member who had more contributions, so I could give him/her an award. To do that I created the following view table, which worked fine since it was for personal use only and I did it at the same date every week.

CREATE VIEW vw_posts AS
    SELECT guild_id, account_id, COUNT(*) AS posts
    FROM public.posts
    WHERE post_date > CURRENT_TIMESTAMP - INTERVAL '1 week'
    GROUP BY(guild_id, account_id)
    ORDER BY posts DESC;

Now I'm doing a new command to show a weekly leaderboard. So after creating the command I quickly realized that my view is fetching in a 7-days interval rather than fetching the current week, so it's fetching data from the previous week.

Small drawing to explain better

I'm getting results like the red line, but I'd like the view to act as the green one.

I did a bit of research but most posts would suggest using date_trunc() or functions alike that wouldn't let me get the rest of the data, I'm definitely struggling to do the query even after reading the documentation.

Thanks for any advice!

Upvotes: 0

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

For the current week use:

CREATE VIEW vw_posts AS
    SELECT guild_id, account_id, COUNT(*) AS posts
    FROM public.posts
    WHERE post_date >= DATE_TRUNC('week', CURRENT_TIMESTAMP)
    GROUP BY guild_id, account_id
    ORDER BY posts DESC;

For the previous week:

CREATE VIEW vw_posts AS
    SELECT guild_id, account_id, COUNT(*) AS posts
    FROM public.posts
    WHERE post_date >= DATE_TRUNC('week', CURRENT_TIMESTAMP) - INTERVAL '1 week' AND
          post_date < DATE_TRUNC('week', CURRENT_TIMESTAMP) 
    GROUP BY guild_id, account_id
    ORDER BY posts DESC;

Upvotes: 1

Related Questions