How to make week start day user configurable(first day of the the week , start date & end date as a-CodePudding

i want to make week first day is user configurable, so user provide the week start day, start date, end date as user parameter in my existing query that returns the week number (existing query return ISO compliant week number, day start from Monday(1) & End day Sunday(7)) on the basis of start date and end date.

Week number i used in my java code to make group of weeks (ex: if start date is 21-11-2022 & end date 05-11-2022 & week start day WEDNESDAY then expected group of dates is 21-11-2022 to 22-11-2022, 23-11-2022 to 29-12-2022,30-11-2022 to 05-12-2022).

what should i change in existing query so it returns exact week number on the basis of user provide start day of week, start date, end date as parameter?

select m.tool_id, m.module_location, alarm_count,  recipe_id, alarm_alias, alarm_issuer_name,
DATE_PART('week',alarm_date) AS week, 
DATE_PART('year',alarm_date) AS yearNo
from alarms.alarm_count a, alarms.alarm_module m, alarms.alarm_issuer ai 
where a.module_uuid = m.module_uuid and a.alarm_issuer_uuid = ai.alarm_issuer_uuid 
and m.module_uuid in (
'027909d4-12dd-4b7d-a391-847f88ee97ab',
 '212277f4-9d05-4465-95f7-a99fcb936451')
and (a.alarm_date) BETWEEN '2022-11-21' and '2022-12-05' 
and severity in ('Critical','Error','Fatal')

CodePudding user response：

CREATE OR REPLACE FUNCTION test 
( IN start_day text -- must be in ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saterday', 'Sunday')
, IN start_date timestamp
, IN end_date timestamp
, OUT tool_id text -- to be replaced by the real column type
, OUT module_location text -- to be replaced by the real column type
, OUT alarm_count text -- to be replaced by the real column type
, OUT recipe_id text -- to be replaced by the real column type
, OUT alarm_alias text -- to be replaced by the real column type
, OUT alarm_issuer_name text -- to be replaced by the real column type
, OUT week double precision
, OUT yearno double precision
)
  RETURNS setof record LANGUAGE plpgsql AS $$
DECLARE 
  i integer ;
BEGIN
  i := array_position(array['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saterday', 'Sunday'], start_day) ;

  IF i IS NOT NULL 
  THEN
    RETURN QUERY
    select m.tool_id, m.module_location, alarm_count,  recipe_id, alarm_alias, alarm_issuer_name,
    CASE 
         WHEN DATE_PART('week', alarm_date) - (CASE WHEN DATE_PART('isodow', alarm_date) < i THEN 1 ELSE 0 END) :: double precision > 1
         THEN DATE_PART('week', alarm_date) - (CASE WHEN DATE_PART('isodow', alarm_date) < i THEN 1 ELSE 0 END) :: double precision
         ELSE DATE_PART('week', alarm_date - (i-1) * interval '1 days')
    END AS Week,
    CASE 
         WHEN DATE_PART('week', alarm_date) - (CASE WHEN DATE_PART('isodow', alarm_date) < i THEN 1 ELSE 0 END) :: double precision > 1
         THEN DATE_PART('isoyear', alarm_date)
         ELSE DATE_PART('isoyear', alarm_date - (i-1) * interval '1 days')
       END AS YearNo
    from alarms.alarm_count a, alarms.alarm_module m, alarms.alarm_issuer ai 
    where a.module_uuid = m.module_uuid and a.alarm_issuer_uuid = ai.alarm_issuer_uuid 
    and m.module_uuid in (
    '027909d4-12dd-4b7d-a391-847f88ee97ab',
    '212277f4-9d05-4465-95f7-a99fcb936451')
    and (a.alarm_date) BETWEEN '2022-11-21' and '2022-12-05' 
    and severity in ('Critical','Error','Fatal') ;
  END IF ;
END ;
$$ ;

see dbfiddle