Decidimos em nosso projeto, devido a necessidade de apontamento de recorrência de horários abertos, dizer por exemplo que um estabelecimento abre de segunda a sexta das 10 AM as 18 PM, utilizar o padrão denominado 'Oppening Hours Expression', segue o exemplo de como isso seria dito nele 'Mo-Fr 10:00-18:00;', após o ';' poderiam ser inseridos na mesma string outras expressões, por exemplo, poderia representar a mesma coisa com 'Mo 10:00-18:00;Tu 10:00-18:00;We 10:00-18:00;Th 10:00-18:00;Fr 10:00-18:00;' sendo a explicação do padrão '<2 primeiras letras do dia da semana em inglês> <hora de inicio em formato ##:##>-<hora de fim em formato ##:##>';
Em nosso projeto resolvemos padronizar o uso das expressões oh (opening hour) do segundo modo apresentado acima ('Mo 10:00-18:00;Tu 10:00-18:00;We 10:00-18:00;Th 10:00-18:00;Fr 10:00-18:00;' ), contendo apenas dias específicos, sem períodos, as funções apresentadas só foram testadas para esses tipos de expressões.
Encontramos bibliotecas que tratam e processam esse tipo de padrão em java script, respondendo por exemplo se em tal dia especifico em tal horário especifico um estabelecimento com tal expressão estará aberto ou fechado, entretanto para se tratar isso em database, o que se viu necessário por questões de fluxo de dados (Não enviar todos os dados para então o frontend o processar com as libs em javascript), não encontramos bibliotecas que processassem a expressão da maneira exemplificada aqui;
Portando, precisamos criar tais funções inicialmente criamos a função
checkoh(oh text, tmsp timestamp) que recebe respectivamente a oh expression 'oh' e o timestamp que se deseja verificar se o mesmo está aberto ou não, segue código para essa se criar essa função em um banco postgre:
CREATE OR REPLACE FUNCTION checkoh(oh text, tmsp timestamp without time zone)
RETURNS boolean
LANGUAGE plpgsql
AS $function$
DECLARE
isOpen BOOLEAN := false;
ohParts INTEGER := max(array_length(regexp_split_to_array(oh, ';'), 1));
counterOh INTEGER := 0;
counterOhTime INTEGER := 0;
tmpTimeStart TIME := null;
tmpTimeEnd TIME := null;
tmspTimePart TIME := tmsp::time;
openAtTmsp BOOLEAN := false;
currentOh text := '';
numberOfTimes integer := 0;
begin
IF (oh is null or tmsp is null) then
return null;
end if;
LOOP
EXIT WHEN counterOh = ohParts + 1;
counterOh := counterOh + 1;
select x[counterOh]
from (
select string_to_array(oh, ';')
) as dt(x) into currentOh;
select
case
when z.daysOfWeekList ~ (regexp_replace(to_char(tmsp, 'Dy'), '.$', '')) then true
else false
end as isDayPresentInOh
from
(
select
case
when y.daysRange = 'Mo-Su' then 'Mo Tu We Th Fr Sa Su'
when y.daysRange = 'Mo-Sa' then 'Mo Tu We Th Fr Sa'
when y.daysRange = 'Mo-Fr' then 'Mo Tu We Th Fr'
when y.daysRange = 'Mo-Th' then 'Mo Tu We Th'
when y.daysRange = 'Mo-We' then 'Mo Tu We'
when y.daysRange = 'Mo-Tu' then 'Mo Tu'
when y.daysRange = 'Mo' then 'Mo'
when y.daysRange = 'Tu-Su' then 'Tu We Th Fr Sa Su'
when y.daysRange = 'Tu-Sa' then 'Tu We Th Fr Sa'
when y.daysRange = 'Tu-Fr' then 'Tu We Th Fr'
when y.daysRange = 'Tu-Th' then 'Tu We Th'
when y.daysRange = 'Tu-We' then 'Tu We'
when y.daysRange = 'Tu' then 'Tu'
when y.daysRange = 'We-Su' then 'We Th Fr Sa Su'
when y.daysRange = 'We-Sa' then 'We Th Fr Sa'
when y.daysRange = 'We-Fr' then 'We Th Fr'
when y.daysRange = 'We-Th' then 'We Th'
when y.daysRange = 'We' then 'We'
when y.daysRange = 'Th-Su' then 'Th Fr Sa Su'
when y.daysRange = 'Th-Sa' then 'Th Fr Sa'
when y.daysRange = 'Th-Fr' then 'Th Fr'
when y.daysRange = 'Th' then 'Th'
when y.daysRange = 'Fr-Su' then 'Fr Sa Su'
when y.daysRange = 'Fr-Sa' then 'Fr Sa'
when y.daysRange = 'Fr' then 'Fr'
when y.daysRange = 'Sa-Su' then 'Sa Su'
when y.daysRange = 'Sa' then 'Sa'
when y.daysRange = 'Su' then 'Su'
else ''
end as daysOfWeekList
from
(
select trim((REGEXP_MATCHES(currentOh, '[A-Z].* ', 'g'))[1]) daysRange
) as y
) as z
INTO openAtTmsp;
--RAISE NOTICE 'openAtTmspDay=% counterOhTime=% numberOfTimes=% currentOh=%', openAtTmsp, counterOhTime, numberOfTimes, currentOh;
IF (openAtTmsp = true) then
numberOfTimes := max(array_length(regexp_split_to_array(currentOh, ':'), 1)) - 1;
loop
exit when counterOhTime = numberOfTimes;
counterOhTime := counterOhTime + 2;
select k.z from
(
select z[1],
Row_Number() Over (Order By z[1]) As RowNum from
(
select REGEXP_MATCHES(currentOh, '[0-9].:..', 'g')
) as dt(z)
) as k
where k.RowNum = counterOhTime - 1
INTO tmpTimeStart;
select k.z from
(
select z[1],
Row_Number() Over (Order By z[1]) As RowNum from
(
select REGEXP_MATCHES(currentOh, '[0-9].:..', 'g')
) as dt(z)
) as k
where k.RowNum = counterOhTime
INTO tmpTimeEnd;
select true where tmspTimePart between tmpTimeStart and tmpTimeEnd into openAtTmsp;
--RAISE NOTICE '% . % numberOfTimes % currentOh %', counterOh, counterOhTime, numberOfTimes, currentOh;
--RAISE NOTICE 'tmpTimeStart % tmpTimeEnd % counterOhTime %', tmpTimeStart, tmpTimeEnd, counterOhTime;
--RAISE NOTICE ' --- ';
IF (openAtTmsp = true) THEN
RETURN true;
END IF;
END LOOP;
END IF;
END LOOP;
RETURN false;
END ;
$function$
;
Embora o tempo e esforço gasto para se criar tal função percebemos, para a realidade de nossa aplicação que não era o suficiente, precisávamos ser capazes de verificar se tal expressão estaria aberta dentro de um período de datas especificas, para isso criamos a função checkohrange(oh text, tmsp timestamp, tmspend timestamp) em relação a anterior essa recebe o parâmetro final a mais 'tmspend' (tmsp sendo a abreviação para timestamp) que representa o fim do período a se buscar e o parâmetro 'tmsp' passa a representar o inicio do período que se busca, segue código para se criar tal função num banco de dados postgre:
CREATE OR REPLACE FUNCTION checkohrange(oh text, tmsp timestamp without time zone, tmspend timestamp without time zone)
RETURNS boolean
LANGUAGE plpgsql
AS $function$
DECLARE
isOpen BOOLEAN := false;
ohParts INTEGER := max(array_length(regexp_split_to_array(oh, ';'), 1));
counterOh INTEGER := 0;
counterOhTime INTEGER := 0;
currentOhTimePartStart TIME := null;
currentOhTimePartEnd TIME := null;
desiredTimePartStart TIME := tmsp::time;
desiredTimePartEnd TIME := tmspend::time;
openAtTmsp BOOLEAN := false;
dateIsOpen BOOLEAN := false;
currentOh text := '';
numberOfTimes integer := 0;
weekDayNameOfRequestedDateStart text := '';
weekDayNameOfRequestedDateEnd text := '';
daysCount INTEGER := tmspend::date - tmsp::date;
currentOhDayRangeName text := '';
begin
IF (oh is null or tmsp is null) then
return null;
end if;
LOOP
EXIT WHEN counterOh = ohParts;
counterOh := counterOh + 1;
select x[counterOh]
from (
select string_to_array(oh, ';')
) as dt(x) into currentOh;
--weed day names start and end (of the requested filter)
select regexp_replace(to_char(tmsp, 'Dy'), '.$', '') into weekDayNameOfRequestedDateStart;
select regexp_replace(to_char(tmspend, 'Dy'), '.$', '') into weekDayNameOfRequestedDateEnd;
select trim((REGEXP_MATCHES(currentOh, '[A-Z].* ', 'g'))[1]) into currentOhDayRangeName;
-- for range
select
case
when y.daysRange = 'Mo' and (y.dayNameStart = 'Mo' or y.dayNameEnd = 'Mo') then true
when y.daysRange = 'Mo' and (y.dayNameStart = 'Su' and y.dayNameEnd = 'Fr') then true
when y.daysRange = 'Mo' and (y.dayNameStart = 'Su' and y.dayNameEnd = 'Th') then true
when y.daysRange = 'Mo' and (y.dayNameStart = 'Su' and y.dayNameEnd = 'We') then true
when y.daysRange = 'Mo' and (y.dayNameStart = 'Su' and y.dayNameEnd = 'Tu') then true
when y.daysRange = 'Mo' and (y.dayNameStart = 'Sa' and y.dayNameEnd = 'Th') then true
when y.daysRange = 'Mo' and (y.dayNameStart = 'Sa' and y.dayNameEnd = 'We') then true
when y.daysRange = 'Mo' and (y.dayNameStart = 'Sa' and y.dayNameEnd = 'Tu') then true
when y.daysRange = 'Mo' and (y.dayNameStart = 'Fr' and y.dayNameEnd = 'We') then true
when y.daysRange = 'Mo' and (y.dayNameStart = 'Fr' and y.dayNameEnd = 'Tu') then true
when y.daysRange = 'Mo' and (y.dayNameStart = 'Th' and y.dayNameEnd = 'Tu') then true
--
--
when y.daysRange = 'Tu' and (y.dayNameStart = 'Tu' or y.dayNameEnd = 'Tu') then true
when y.daysRange = 'Tu' and (y.dayNameStart = 'Sa' and y.dayNameEnd = 'Mo') then true
when y.daysRange = 'Tu' and (y.dayNameStart = 'Fr' and y.dayNameEnd = 'Mo') then true
when y.daysRange = 'Tu' and (y.dayNameStart = 'Th' and y.dayNameEnd = 'Mo') then true
when y.daysRange = 'Tu' and (y.dayNameStart = 'We' and y.dayNameEnd = 'Mo') then true
when y.daysRange = 'Tu' and (y.dayNameStart = 'Su' and y.dayNameEnd = 'We') then true
when y.daysRange = 'Tu' and (y.dayNameStart = 'Su' and y.dayNameEnd = 'Th') then true
when y.daysRange = 'Tu' and (y.dayNameStart = 'Su' and y.dayNameEnd = 'Fr') then true
when y.daysRange = 'Tu' and (y.dayNameStart = 'Sa' and y.dayNameEnd = 'Th') then true
when y.daysRange = 'Tu' and (y.dayNameStart = 'Sa' and y.dayNameEnd = 'We') then true
when y.daysRange = 'Tu' and (y.dayNameStart = 'Fr' and y.dayNameEnd = 'We') then true
--
--
when y.daysRange = 'We' and (y.dayNameStart = 'We' or y.dayNameEnd = 'We') then true
when y.daysRange = 'We' and (y.dayNameStart = 'Su' and y.dayNameEnd = 'Th') then true
when y.daysRange = 'We' and (y.dayNameStart = 'Su' and y.dayNameEnd = 'Fr') then true
when y.daysRange = 'We' and (y.dayNameStart = 'Sa' and y.dayNameEnd = 'Th') then true
when y.daysRange = 'We' and (y.dayNameStart = 'Mo' and y.dayNameEnd = 'Th') then true
when y.daysRange = 'We' and (y.dayNameStart = 'Mo' and y.dayNameEnd = 'Fr') then true
when y.daysRange = 'We' and (y.dayNameStart = 'Mo' and y.dayNameEnd = 'Sa') then true
when y.daysRange = 'We' and (y.dayNameStart = 'Tu' and y.dayNameEnd = 'Th') then true
when y.daysRange = 'We' and (y.dayNameStart = 'Tu' and y.dayNameEnd = 'Fr') then true
when y.daysRange = 'We' and (y.dayNameStart = 'Tu' and y.dayNameEnd = 'Sa') then true
when y.daysRange = 'We' and (y.dayNameStart = 'Tu' and y.dayNameEnd = 'Su') then true
--
--
when y.daysRange = 'Th' and (y.dayNameStart = 'Th' or y.dayNameEnd = 'Th') then true
when y.daysRange = 'Th' and (y.dayNameStart = 'Mo' and y.dayNameEnd = 'Fr') then true
when y.daysRange = 'Th' and (y.dayNameStart = 'Mo' and y.dayNameEnd = 'Sa') then true
when y.daysRange = 'Th' and (y.dayNameStart = 'Tu' and y.dayNameEnd = 'Fr') then true
when y.daysRange = 'Th' and (y.dayNameStart = 'Tu' and y.dayNameEnd = 'Sa') then true
when y.daysRange = 'Th' and (y.dayNameStart = 'Tu' and y.dayNameEnd = 'Su') then true
when y.daysRange = 'Th' and (y.dayNameStart = 'We' and y.dayNameEnd = 'Fr') then true
when y.daysRange = 'Th' and (y.dayNameStart = 'We' and y.dayNameEnd = 'Sa') then true
when y.daysRange = 'Th' and (y.dayNameStart = 'We' and y.dayNameEnd = 'Su') then true
when y.daysRange = 'Th' and (y.dayNameStart = 'Su' and y.dayNameEnd = 'Fr') then true
--
--
when y.daysRange = 'Fr' and (y.dayNameStart = 'Fr' or y.dayNameEnd = 'Fr') then true
when y.daysRange = 'Fr' and (y.dayNameStart = 'Mo' and y.dayNameEnd = 'Sa') then true
when y.daysRange = 'Fr' and (y.dayNameStart = 'Tu' and y.dayNameEnd = 'Sa') then true
when y.daysRange = 'Fr' and (y.dayNameStart = 'Tu' and y.dayNameEnd = 'Su') then true
when y.daysRange = 'Fr' and (y.dayNameStart = 'We' and y.dayNameEnd = 'Sa') then true
when y.daysRange = 'Fr' and (y.dayNameStart = 'We' and y.dayNameEnd = 'Su') then true
when y.daysRange = 'Fr' and (y.dayNameStart = 'Th' and y.dayNameEnd = 'Sa') then true
when y.daysRange = 'Fr' and (y.dayNameStart = 'Th' and y.dayNameEnd = 'Su') then true
when y.daysRange = 'Fr' and (y.dayNameStart = 'Th' and y.dayNameEnd = 'Tu') then true
when y.daysRange = 'Fr' and (y.dayNameStart = 'Th' and y.dayNameEnd = 'Mo') then true
when y.daysRange = 'Fr' and (y.dayNameStart = 'We' and y.dayNameEnd = 'Mo') then true
--
--
when y.daysRange = 'Sa' and (y.dayNameStart = 'Sa' or y.dayNameEnd = 'Sa') then true
when y.daysRange = 'Sa' and (y.dayNameStart = 'Tu' and y.dayNameEnd = 'Su') then true
when y.daysRange = 'Sa' and (y.dayNameStart = 'We' and y.dayNameEnd = 'Su') then true
when y.daysRange = 'Sa' and (y.dayNameStart = 'Th' and y.dayNameEnd = 'Su') then true
when y.daysRange = 'Sa' and (y.dayNameStart = 'Fr' and y.dayNameEnd = 'Su') then true
when y.daysRange = 'Sa' and (y.dayNameStart = 'Fr' and y.dayNameEnd = 'We') then true
when y.daysRange = 'Sa' and (y.dayNameStart = 'Fr' and y.dayNameEnd = 'Tu') then true
when y.daysRange = 'Sa' and (y.dayNameStart = 'Fr' and y.dayNameEnd = 'Mo') then true
when y.daysRange = 'Sa' and (y.dayNameStart = 'Th' and y.dayNameEnd = 'Tu') then true
when y.daysRange = 'Sa' and (y.dayNameStart = 'Th' and y.dayNameEnd = 'Mo') then true
when y.daysRange = 'Sa' and (y.dayNameStart = 'We' and y.dayNameEnd = 'Mo') then true
--
--
when y.daysRange = 'Su' and (y.dayNameStart = 'Su' or y.dayNameEnd = 'Su') then true
when y.daysRange = 'Su' and (y.dayNameStart = 'Sa' and y.dayNameEnd = 'Mo') then true
when y.daysRange = 'Su' and (y.dayNameStart = 'Sa' and y.dayNameEnd = 'Tu') then true
when y.daysRange = 'Su' and (y.dayNameStart = 'Sa' and y.dayNameEnd = 'We') then true
when y.daysRange = 'Su' and (y.dayNameStart = 'Sa' and y.dayNameEnd = 'Th') then true
when y.daysRange = 'Su' and (y.dayNameStart = 'Fr' and y.dayNameEnd = 'We') then true
when y.daysRange = 'Su' and (y.dayNameStart = 'Fr' and y.dayNameEnd = 'Tu') then true
when y.daysRange = 'Su' and (y.dayNameStart = 'Fr' and y.dayNameEnd = 'Mo') then true
when y.daysRange = 'Su' and (y.dayNameStart = 'Th' and y.dayNameEnd = 'Tu') then true
when y.daysRange = 'Su' and (y.dayNameStart = 'Th' and y.dayNameEnd = 'Mo') then true
when y.daysRange = 'Su' and (y.dayNameStart = 'We' and y.dayNameEnd = 'Mo') then true
--
else false
end as isBetweenDayWeekStartAndEnd
from
(
select currentOhDayRangeName daysRange,
--trim((REGEXP_MATCHES((regexp_replace(to_char(tmsp, 'Dy'), '.$', '')), '[A-Z].* ', 'g'))[1]) dayNameStart,
--trim((REGEXP_MATCHES((regexp_replace(to_char(tmspend, 'Dy'), '.$', '')), '[A-Z].* ', 'g'))[1]) dayNameEnd,
weekDayNameOfRequestedDateStart dayNameStart,
weekDayNameOfRequestedDateEnd dayNameEnd,
daysCount daysCount
) as y
INTO dateIsOpen;
raise notice '%', dateIsOpen;
--RAISE NOTICE 'openAtTmspDay=% counterOhTime=% numberOfTimes=% currentOh=%', openAtTmsp, counterOhTime, numberOfTimes, currentOh;
IF (dateIsOpen = true) then
IF (tmspend::date - tmsp::date >= 6) THEN
RETURN true;
END IF;
IF (tmspend::date - tmsp::date = 0) then
numberOfTimes := max(array_length(regexp_split_to_array(currentOh, ':'), 1)) - 1;
loop
exit when counterOhTime = numberOfTimes;
counterOhTime := counterOhTime + 2;
select k.z from
(
select z[1],
Row_Number() Over (Order By z[1]) As RowNum from
(
select REGEXP_MATCHES(currentOh, '[0-9].:..', 'g')
) as dt(z)
) as k
where k.RowNum = counterOhTime - 1
INTO currentOhTimePartStart;
select k.z from
(
select z[1],
Row_Number() Over (Order By z[1]) As RowNum from
(
select REGEXP_MATCHES(currentOh, '[0-9].:..', 'g')
) as dt(z)
) as k
where k.RowNum = counterOhTime
INTO currentOhTimePartEnd;
if (openAtTmsp = false) then
select true where currentOhTimePartStart between desiredTimePartStart and desiredTimePartEnd into openAtTmsp;
raise notice '% between % and % -- %;', currentOhTimePartStart, desiredTimePartStart, desiredTimePartEnd, openAtTmsp;
end if;
if (openAtTmsp = false) then
select true where currentOhTimePartEnd between desiredTimePartStart and desiredTimePartEnd into openAtTmsp;
raise notice '% , % , % -- %;', currentOhTimePartEnd, desiredTimePartStart, desiredTimePartEnd, openAtTmsp;
end if;
if (openAtTmsp = false) then
select true where desiredTimePartStart >= currentOhTimePartStart and desiredTimePartEnd >= currentOhTimePartEnd into openAtTmsp;
raise notice '% , % , % -- %;', currentOhTimePartEnd, desiredTimePartStart, desiredTimePartEnd, openAtTmsp;
end if;
if (openAtTmsp = true) then
RETURN true;
end if;
END LOOP;
END IF;
-- raise notice '%', tmspend::date - tmsp::date > 1;
IF (tmspend::date - tmsp::date > 0) then
if (currentOhDayRangeName != weekDayNameOfRequestedDateStart and currentOhDayRangeName != weekDayNameOfRequestedDateEnd) then
return true;
end if;
if (currentOhDayRangeName = weekDayNameOfRequestedDateStart) then
if ((select true where currentOhTimePartStart between desiredTimePartStart and desiredTimePartEnd) = true) then
return true;
end if;
end if;
if (currentOhDayRangeName = weekDayNameOfRequestedDateEnd) then
if ((select true where currentOhTimePartEnd between desiredTimePartStart and desiredTimePartEnd) = true) then
return true;
end if;
end if;
return true;
END IF;
END IF;
counterOhTime := 0;
END LOOP;
RETURN false;
END ;
$function$
;
Em caso de vontade de melhorar tais funções recomendo o uso da função 'raise notice', essa função permite gerar outputs de texto ao se executar a função, exemplos de uso são encontradas comentadas nas funções acima e o output, utilizando o software 'DBeaver' aparece na aba 'Output' da janela de execução de queryes.
Espero ter ajudado (:
Espero ter ajudado (:
