Bug 6970 - support US week number.
Summary: support US week number.
Status: NEW
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: 11.37.11 (Jun2020-SP1)
Hardware: x86_64 (amd64/em64t) All
: Normal normal
Assignee: SQL devs
Depends on:
Reported: 2020-09-15 08:16 CEST by william.jing
Modified: 2020-09-15 09:51 CEST (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
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

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.