CODEWITHSUNDEEP
sqlpostgresql
GRS
GRS

Reputation: 3094

How to get a week number for last N days in PostgreSQL?

I have an items table which cointains a created date field. I would like to look at the orders for the past N weeks, and run a SELECT query which can tell me which week the item was created at.

For example, something like:

SELECT *,
WEEK_START(created) as week_beginning,
WEEK_NUMBER(created) as week_number
FROM items
WHERE created > current_date - N * 7  ## look at last N weeks only

I would expect the same columns, but now, additionaly, I will have a week_number and week_beginning column ranging from 1 to N, where N is the last week and week_beginning will tell me the time the week began.

Upvotes: 0

Views: 425

Answers (1)

sticky bit
sticky bit

Reputation: 37487

You can use extract(isodow FROM <date>) to extract the weekday from a date as a number -- from 1 for Monday to 7 for Sunday. If you subtract one less than that number of days from the date you have the Monday of the week the date is in. There you have your start of the week and can calculate your week number by subtracting the difference between the first days of the week and dividing that by 7. (You could also use extract(week FROM <date>) to get the week number and do your calculations from there, but that would be more complicate between the years especially if "leap weeks" come into play too.)

SELECT *,
       ((current_date - (extract(isodow FROM current_date) - 1 || ' day')::interval)::date
         - (created - (extract(isodow FROM created) - 1 || ' day')::interval)::date)
       / 7 + 1 week_number,
       (created - (extract(isodow FROM created) - 1 || ' day')::interval)::date first_day_of_week
       FROM items
       WHERE created > current_date - (extract(isodow FROM current_date) + 7 * (2 - 1) || 'day')::interval
       ORDER BY created;

db<>fiddle

I changed the WHERE clause, so the result gets snapped to a whole week rather than just a multiple of seven days. If you don't want that, replace it with your old WHERE clause. If you want to keep my WHERE clause, you may want to change the (N - 1) to just N, if the current week doesn't count. In that case you may also want to remove the + 1 from the calculation of the week number, so that the current week gets the number 0.

Upvotes: 1

Related Questions