Bug 6970

Summary: support US week number.
Product: SQL Reporter: william.jing
Component: allAssignee: SQL devs <bugs-sql>
Status: NEW ---    
Severity: normal    
Priority: Normal    
Version: 11.37.11 (Jun2020-SP1)   
Hardware: x86_64 (amd64/em64t)   
OS: All   

Description william.jing 2020-09-15 08:16:51 CEST
User-Agent:       Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/85.0.4183.102 Safari/537.36
Build Identifier: 

hi, in our monetdb , we always use week(date'2012-12-30') to get ISO8601 week number, but we don't have a function that could get the US week number. 

Reproducible: Always
Comment 1 Sjoerd Mullender cwiconfidential 2020-09-15 09:08:32 CEST
Could you use
(dayofyear(date'2012-12-30') - 1) / 7 + 1
?

You could stick this into a function:
create function usweek(dt date) returns integer begin return (dayofyear(dt) - 1) / 7 + 1; end
Comment 2 william.jing 2020-09-15 09:48:41 CEST
i get us week :
select   sql_add(date'2016-01-23', 3600*24*(-dayofweek(date'2016-01-23')%7));---get the day of Sunday in the current period (first day is Sunday)
select   sql_add(date'2016-01-23', 3600*24*(-dayofweek(date'2016-01-23')%7+6));---get the day of Saturday in the current period (first day is Sunday)
sql_add(date'2016-01-23', 3600*24*(-dayofweek(date'2016-01-23')%7+6)))+6  --- need to add correction factor 

(dayofyear(sql_add(date'2016-01-23', 3600*24*(-dayofweek(date'2016-01-23')%7+6)))+6)/7 ---get the us week number


ref:
https://www.sqlservercentral.com/articles/a-simple-formula-to-calculate-the-iso-week-number
https://en.wikipedia.org/wiki/Week
https://www.timeanddate.com/calendar/custom.html?year=2012&country=1&wno=1&holm=1&df=1
Comment 3 william.jing 2020-09-15 09:51:24 CEST
i use other functions to calculate the us week number, but the performance is not very well. for example, 3.4M rows,  if i use week function to get iso week number, it will take 500ms, but when i calculate us week number, it will take about 2s.