Clash Royale CLAN TAG

Get week number and start date and end date between two given dates in postgresql

I am trying to create a function that calculates the week number between two dates. For example, the function received two dates 2018-07-02 and 2018-07-27. So The return I want to is

2018-07-02

2018-07-27

week_id | week_start_date | week_end_date
week1 | 2018-07-02 | 2018-07-08
week2 | 2018-07-09 | 2018-07-15
week3 | 2018-07-16 | 2018-07-22
week4 | 2018-07-23 | 2018-07-27


I was able to do the calculation, but how may I return the result? The code I have done is below. Any help will be appreciated.

--DROP FUNCTION public.find_week_numebr_by_date(integer, timestamp without time zone, timestamp without time zone);
CREATE OR REPLACE FUNCTION public.find_week_numebr_by_dates(
v_institution_id integer default null::integer,
v_start_time timestamp without time zone default null:: timestamp without time zone,
v_end_time timestamp without time zone default null:: timestamp without time zone
)

RETURNS integer--TABLE(week_id varchar, week_start_date date, week_end_Date date)
LANGUAGE 'plpgsql'

COST 100
VOLATILE SECURITY DEFINER
--ROWS 1000
AS $BODY$

declare
v_term_name varchar;
v_term_end_date date;
v_number_of_weeks integer;
v_week_name varchar;
v_week_start_date date;
v_week_end_date date;
begin
SELECT term_name
INTO v_term_name
FROM find_institution_term_for_date(v_institution_id, v_start_time::date);

SELECT (EXTRACT(days FROM (v_end_time::TIMESTAMP - v_start_time::TIMESTAMP)) / 7)::int into v_number_of_weeks;

v_term_end_date = v_end_time::date;

v_week_start_date = v_start_time::date;
v_week_end_date = v_week_start_date::date + interval '6' day;

for i in 1..(v_number_of_weeks) loop
v_week_name = v_term_name || '_week' || i ;

IF(v_week_end_date > v_term_end_date::date) THEN
v_week_end_date = v_term_end_date::date;
END IF;

raise notice '%,%,%',v_week_name, v_week_start_date, v_week_end_date;

v_week_start_date = v_start_time::date + interval '7' day;
v_week_end_date = v_week_start_date::date + interval '6' day;

v_start_time = v_week_start_date;

end loop;
return 1;
end;
$BODY$;


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.