How to get (year of week number, week number) for a date set

user_3874475 0
Viewed 2905

I would like to get (year of week number, week number) tuplets for a set of dates.

In Impala SQL, there is function weekofyear which gives the correct week number:

select year('2018-12-31'), weekofyear('2018-12-31')

year('2018-12-31')|weekofyear('2018-12-31')|
------------------|------------------------|
              2018|                       1|

But function year does not work if one wants to order (year, week number) tuplets in string format 'yyyyWwk'.

Is there a neat way to get 'yyyyWwk' for each date? Date 2018-12-31 given in the above example would yield result 2019W01.

This does not work:

select concat(cast(year(dt) as STRING),'W',lpad(cast(weekofyear(dt) as string),2,"0")) as wk, dt
  from (
select '2018-11-30' as dt 
union 
select '2018-12-31' as dt
union 
select '2019-01-31' as dt
       ) as t
 order by 1, 2;

as it does not order the dates correctly:

wk     |dt        |
-------|----------|
2018W01|2018-12-31|
2018W48|2018-11-30|
2019W05|2019-01-31|

How to get a "week-year"=2019 for the date 2018-12-31?

impala week-number sql
5 Answers

If this is just an order by issue, then I speculate that you have some sort of aggregation query. If so, you can use:

order by min(datecol)

This will order the overall query correctly.

That said, if you want information by week, then use date_trunc() instead of extracting the year and week:

select date_trunc('week', '2018-12-31')
user_11440350

You can use column aliases in the order by clause

select year(datecol) as yr, weekofyear(datecol) as wk
  from yourtable
 order by yr, wk

as both year() and weekofyear() functions return integer values.

Aliases are allowed at the top level of the GROUP BY, HAVING, and ORDER BY clauses, e.g. GROUP BY alias from the documentation

user_58413060

I was trying to do something similar to you above and got caught out by the fact that 1 Jan 2021 falls into year 2021 but weekofyear 53!

The fix for you (and me) would be to find the monday in that week (if we're on a sunday, we go forward a day!) and then calculate the weekofyear and year from that. The impala code is which uses dayofweek and days_sub to get to the monday of the week (or if we're already on a monday, stay on it). We do -2 because dayofweek for monday itself is 2.

I originally chose Sunday to go with, but the weekofyear in impala changes on a Monday, so Monday is better

with dts as (
    select '2018-11-30' as dt 
    union 
    select '2018-12-31' as dt
    union 
    select '2019-01-01' as dt
    union     
    select '2019-01-31' as dt
    union
    select '2020-12-30' as dt
    union
    select '2020-12-31' as dt
    union
    select '2021-01-01' as dt
    union
    select '2021-01-02' as dt
    union
    select '2021-01-03' as dt
    union
    select '2021-01-04' as dt
    union    
    select '2021-01-09' as dt    
    union
    select '2021-01-10' as dt
    union
    select '2021-01-11' as dt
),
step1 as (
    select
        dt,
        dayofweek(dt) as dayweek,
         dayofweek(dt)-2,
        days_sub(dt, dayofweek(dt)-2) as monOfWeek,
        days_sub(dt, dayofweek(dt)-1) as sunOfWeek
    from dts
)
select 
    dt,
    weekofyear(monOfWeek) as monweeknum,
    year(monofWeek) monyear
from step1 t
order by dt;

resulting in:

dt monweeknum monyear 2018-11-30 48 2018
2018-12-31 1 2018
2019-01-01 1 2018
2019-01-31 5 2019
2020-12-30 53 2020
2020-12-31 53 2020
2021-01-01 53 2020
2021-01-02 53 2020
2021-01-03 1 2021
2021-01-04 1 2021
2021-01-09 1 2021
2021-01-10 2 2021
2021-01-11 2 2021

Bit more information for why this craziness happens is in https://en.wikipedia.org/wiki/ISO_week_date

and an interactive calculator in https://www.timeanddate.com/date/weeknumber.html

user_46950380

Late to the question but was looking at the problem and by using string format we can go directly to ISO-weeks (iw), also using ISO-year (iyyy).

Example query:

select cast(cast('20210124' as date format 'YYYYMMDD') as string format 'iyyyiW')

Returns

202103
user_73863320

select to_date(date_sub(to_date(cast('2021-11-23' as timestamp ) ),(dayofweek(cast('2021-11-23' as timestamp))-2))) week_start_date

Note :- week start on Monday then - 2 in the above formula

user_174883330
Related