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
standard week looks ok but israel week was wrong, how can I make it correct?
