postgresql 实现计算日期间隔排除周末节假日方案

前置条件：需要维护一张节假日日期表。例如创建holiday表保存当年假期日期

CREATE TABLE `holiday` (
	`id` BIGINT(10) ZEROFILL NOT NULL DEFAULT 0,
	`day` TIMESTAMP NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
COMMENT='假期表'
COLLATE='utf8mb4_0900_ai_ci'
;

返回日期为xx日xx时xx分格式，可以在此基础上调整格式

-- FUNCTION: public.get_timedelay(timestamp with time zone, timestamp with time zone)

-- DROP FUNCTION IF EXISTS public.get_timedelay(timestamp with time zone, timestamp with time zone);

CREATE OR REPLACE FUNCTION public.get_timedelay(
	starttime timestamp with time zone,
	endtime timestamp with time zone)
    RETURNS text
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
	 v_return  varchar;--返回间隔时间 xx日xx时xx分
	 v_minute  integer;--间隔分钟
	 v_hour integer;
	 v_temp_minute integer;
	 v_temp_hour