Week Day Starting from a Certain Day (01 Jan 2021) in Postgres-CodePudding

I am trying to get week numbers in a Year starting from a certain day I've checked the stack but quite confused.

SELECT EXTRACT(WEEK FROM TIMESTAMP '2021-01-01'),
       extract('year' from TIMESTAMP '2021-01-01')

The output is 53|2021

I want it to be 01|2021

I understand the principle of the isoweek but I want the year to start in 01-01-2021

The aim is to use intervals from this day to determine week numbers

Week N0| End Date
     1 | 01-01-2021
     2 | 01-08-2021
     5 | 01-29-2021
...

CodePudding user response：

This is really strange way to determine the week number, but in the end it's a simple math operation: the number of days since January first divided by 7.

You can create a function for this:

create function custom_week(p_input date)
  returns int
as
$$
   select (p_input - date_trunc('year', p_input)::date) / 7   1;
$$
language sql
immutable;

So this:

select date, custom_week(date)
from (
  values 
    (date '2021-01-01'), 
    (date '2021-01-08'), 
    (date '2021-01-29')
) as v(date)

yields

date       | custom_week
----------- ------------
2021-01-01 |           1
2021-01-08 |           2
2021-01-29 |           5