Group date by week number in the form YYYY-MM-WW-CodePudding

Currently have rows aggregated by week number.

SELECT to_char(date, 'IYYY-MM-IW') AS week, from TABLE GROUP BY week

The results will show the form "2021-07-29". Is it possible to change the week number such that it is the number week of the month (instead of year).

For example, instead of "2021-07-29", we convert to "2021-07-04" since the 29th week of the year is actually the 4th week of the month.

CodePudding user response：

W week of month (1–5) (the first week starts on the first day of the month)

So you can use:

to_char(date, 'YYYY-MM-W')

For e.g 2021-10-18 this yields 2021-10-3 (third week in October)