sql - Get rows with timestamp from current week in Postgres?

Question

I'm trying to build a weekly leaderboard of sorts and was wondering how I could get the rows with a timestamp that is within the current week (Monday to Sunday). I've tried:

SELECT id, COUNT(*) FROM Data WHERE created::date BETWEEN date $1 and date $2 GROUP BY id ORDER BY COUNT(*) DESC LIMIT 10;

But got stuck on how I could get the rows within the current week without hard coding them. created is a column of type TIMESTAMP.

I saw that there was something called YEARWEEK() in MySQL. Is there an equivalent in Postgres? If not, what can I do to get the desired result?

Answer 1

You can use date_trunc() with "week":

where created >= date_trunc('week', now())

This assumes that no created timestamps are in the future. Postgres follows the ISO standard of having weeks start on Mondays, which is what you want.