Posgresql standard week of year to israel week of year

Viewed 20

I have a query to select data and group by date(data year by years), I need to convert select data from standard week(monday to sunday) to israel week(which start from sunday and end on saturday) my query:

select
    data_date, 
    CONCAT(to_char(data_date::date, 'IYYY'), 'W', to_char(data_date::date, 'IW')) as standard_week,
    CONCAT(to_char(data_date::date, 'IYYY'), 'W', floor(EXTRACT(doy FROM data_date::TIMESTAMP)/7 + 1)) as israel_week
from
    data
where
    data_date >= '2022-01-01'
    and data_date <= '2022-01-31'
group by data_date, standard_week, israel_week
order by data_date

the result like this:
enter image description here

standard week looks ok but israel week was wrong, how can I make it correct?

0 Answers
Related