Método para pesquisa de dados em período de DATA com cliente de Time Zone imprevisivel

1_ddl.sql

/*
Método para pesquisa de dados em período de DATA com cliente de Time Zone imprevisivel.

Autor: Moisés Paes Sena (https://github.com/moisespsena)
*/

DROP TABLE IF EXISTS logs CASCADE;

/*
COLUNAS:
 ID: TIMESTAMP - sempre em UTC (este é o segredo). Pode-se colocar em um TRIGGER.
 tsz: TIMESTAMPTZ - apenas para validação na hora local, o PG converte automaticamente para a TZ LOCAL.
 tsz_val: TEXT - apenas para conferir com a TZ cadastrada.
*/
CREATE TABLE logs (
  id TIMESTAMP NOT NULL PRIMARY KEY, 
  tsz TIMESTAMPTZ, 
  tsz_val TEXT
);

2_data_generator.sql

/*
Insere 10 dias de dados horarios, convertendo o input para UTC.
*/
INSERT INTO logs 
	(id, tsz, tsz_val)
	SELECT 
		dt::TIMESTAMPTZ AT TIME ZONE 'UTC' -- converte para UTC
		, dt::TIMESTAMPTZ                  -- grava em TZ mesmo
		, dt                               -- grava apenas o texto
	FROM (
		SELECT ('2021-01-01 00:00:00'::TIMESTAMP + make_interval(hours => i))::TEXT || '-03:00'
		FROM
	 	generate_series(0, 24*10) AS t(i)
	) as r(dt);

3_function.sql

/*
ARGUMENTOS: 
 fd: From Date - Data de Inicio
 td: To Date   - Data Final
 tz: Time Zone - O Time Zone da interface do cliente (browser etc), 
                 não precisa ser igual ao cadastrado, pq a única coisa
                 que importa é a coluna ID que está em UTC.
		 
		 OBS: para pesquisar em UTC, tanto faz '-00:00' como '+00:00'.
*/
create or replace function logs_of_date_range(fd DATE, td DATE, tz TEXT) RETURNS SETOF logs AS $$
DECLARE
	r logs;
	t TEXT;
	neg BOOL;
	start_ts TIMESTAMP;
	end_ts TIMESTAMP;
BEGIN
	neg = substring(tz, 1, 1) = '-';
	t = substring(tz, 2);
	
	IF neg THEN
		start_ts = (fd || ' ' || t)::TIMESTAMP;
		end_ts = ((td || ' ' || t )::TIMESTAMP + make_interval(days => 1))::TIMESTAMP;
		
		FOR r IN 
			SELECT * FROM logs 
			WHERE id >= start_ts 
				AND id < end_ts
			ORDER BY id
		LOOP
			RETURN NEXT r;
		END LOOP;
	ELSE
		start_ts = fd::TIMESTAMP - t::TIME;
		end_ts = td::TIMESTAMP - t::TIME + make_interval(days => 1);
		
		FOR r IN 
			SELECT * FROM logs 
			WHERE id >= start_ts 
				AND id < end_ts
			ORDER BY id
		LOOP
			RETURN NEXT r;
		END LOOP;
	END IF;
	
	RETURN;
END;
$$ LANGUAGE PLPGSQL VOLATILE;

4_queries.sql

-- Consulta 1: Um único dia e Time Zone IGUAL ao Cadastro
select * from logs_of_date_range('2021-01-03', '2021-01-03', '-03:00');

-- Consulta 2: Intervalo de dia e Time Zone IGUAL ao Cadastro
select * from logs_of_date_range('2021-01-03', '2021-01-05', '-03:00');


-- Consulta 3: Um único dia e Time Zone DIFERENTE do Cadastro
select * from logs_of_date_range('2021-01-03', '2021-01-03', '+03:00');

-- Consulta 4: Intervalo de dia e Time Zone DIFERENTE ao Cadastro
select * from logs_of_date_range('2021-01-03', '2021-01-05', '+03:00');