So I have a shell script which returns me the week number using an SQL query :
select TO_CHAR(TO_DATE('01/01/2022','DD/MM/YYYY'),'fmWW') as WEEK from dual;
This query returns 1. (Starting at 1st Jan)
Python code: I have tried the following however it returns 52 rather than 1 :
week=datetime.date(2022,1,1).isocalendar()[1]
Week starting from Mon-Sun
Is there another way in which I can get the week number of year based on starting at 1st Jan so I get 1 for 01/01/2022?
Required output:
Week ending Jan 2 2022 = 1
Week ending Jan 9 2022 = 2
Week ending Jan 16 2022 = 3
CodePudding user response：
Is there another way in which I can get the week number of year based on starting at 1st Jan so I get 1 for 01/01/2022?
You can use, for a given date dt, as you said in the question:
TO_CHAR(dt, 'fmWW')
or can calculate it using:
FLOOR((dt - TRUNC(dt, 'YY'))/7) 1
or, to match the python code, can get the ISO week using:
TO_CHAR(dt, 'fmIW')
Or, if you want to start counting the week from the 1st January and change weeks on a Monday then:
FLOOR((dt - TRUNC(TRUNC(dt, 'YY'), 'IW'))/7) 1
Then the query:
WITH sample_dates (dt) AS (
SELECT DATE '2021-12-30' LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 40
)
SELECT dt,
TO_CHAR(dt, 'fmWW') AS week,
TO_CHAR(dt, 'fmIW') AS isoweek,
FLOOR((dt - TRUNC(dt, 'YY'))/7) 1 AS weekfromyearstart,
FLOOR((dt - TRUNC(TRUNC(dt, 'YY'), 'IW'))/7) 1 AS montosunweekfromyearstart
FROM sample_dates
Outputs:
DT WEEK ISOWEEK WEEKFROMYEARSTART MONTOSUNWEEKFROMYEARSTART 2021-12-30 52 52 52 53 2021-12-31 53 52 53 53 2022-01-01 1 52 1 1 2022-01-02 1 52 1 1 2022-01-03 1 1 1 2 2022-01-04 1 1 1 2 2022-01-05 1 1 1 2 2022-01-06 1 1 1 2 2022-01-07 1 1 1 2 2022-01-08 2 1 2 2 2022-01-09 2 1 2 2 2022-01-10 2 2 2 3 2022-01-11 2 2 2 3 2022-01-12 2 2 2 3 2022-01-13 2 2 2 3 2022-01-14 2 2 2 3 2022-01-15 3 2 3 3 2022-01-16 3 2 3 3 2022-01-17 3 3 3 4 2022-01-18 3 3 3 4 2022-01-19 3 3 3 4 2022-01-20 3 3 3 4 2022-01-21 3 3 3 4 2022-01-22 4 3 4 4 2022-01-23 4 3 4 4 2022-01-24 4 4 4 5 2022-01-25 4 4 4 5 2022-01-26 4 4 4 5 2022-01-27 4 4 4 5 2022-01-28 4 4 4 5 2022-01-29 5 4 5 5 2022-01-30 5 4 5 5 2022-01-31 5 5 5 6 2022-02-01 5 5 5 6 2022-02-02 5 5 5 6 2022-02-03 5 5 5 6 2022-02-04 5 5 5 6 2022-02-05 6 5 6 6 2022-02-06 6 5 6 6 2022-02-07 6 6 6 7
db<>fiddle here
If you want a Python function then:
import datetime
def week_from_year_start(dt: datetime.date) -> int:
year_start = dt.replace(month=1, day=1)
return int((dt - year_start).days/7) 1
or:
def mon_sun_week_from_year_start(dt: datetime.date) -> int:
year_start = dt.replace(month=1, day=1)
week_start = year_start - datetime.timedelta(days=year_start.weekday())
return int((dt - week_start).days/7) 1
CodePudding user response：
You are using isocalendar(), so ISO week definition: so every week has one single number (which should be used with the year): in such case, you are still in the last week of 2021.
I do not find a simple solution, but you can use this ugly trick:
this_date = datetime.date(2022,1,1)
week = int(this_date.strftime("%W")) int(this_date.weekday() != 0)
How it work: python has three weeks definitions:
%U: Week number of the year (Sunday as the first day of the week) as a zero-padded decimal number. All days in a new year preceding the first Sunday are considered to be in week 0.
%W: Week number of the year (Monday as the first day of the week) as a zero-padded decimal number. All days in a new year preceding the first Monday are considered to be in week 0.
%V: ISO 8601 week as a decimal number with Monday as the first day of the week. Week 01 is the week containing Jan 4.
%W seems a good candidate, but it starts with 0 until the first Monday.
So we may add 1, but then it fails when the first day of year is Monday. The second part is just a test for Monday. Remember that an the integer of a boolean is 0 for False and 1 for True. You may prefer to use an if else operator.
If you prefer to have weeks numbered from Sunday, just replace %W with %U.