On 6/7/21 3:12 PM, David Gauthier wrote:
> Hi:
>
> I suspect I'm not the first to ask about this but couldn't find anything
> after googling for a bit. So here goes....
>
> I'd like to get the "age" difference between two times which span either
> all or part of a weekend but exclude any time that transpired during the
> weekend.
> Example (please pardon the non-timestamp vals here...)
>
> age('Monday-Noon','Prev-Friday-Noon')
> would give me '1 day'.
>
> ...and...
>
> age('Sunday-Noon','Prev-Friday-Noon')
> would give me '12 hours'
>
> You get the picture.
>
> Has this wheel already been invented ?
> I don't see an easy way to do this off-hand.
> All Ears :-)
Perhaps not the prettiest of solutions, but what about something like this?
8<----------------------------------------
CREATE OR REPLACE FUNCTION nonweekendhours(startts timestamptz, endts
timestamptz)
RETURNS interval AS $$
SELECT
(SUM(case when extract(dow from g.ts) > 0
and extract(dow from g.ts) < 6 then
1
else
0 end) || ' hours')::interval
FROM generate_series(startts, endts - '1 hour'::interval,'1 hour') AS
g(ts)
$$ LANGUAGE sql;
SELECT nonweekendhours('2021-06-04 12:00:00','2021-06-06 12:00:00');
nonweekendhours
-----------------
12:00:00
(1 row)
SELECT nonweekendhours('2021-06-04 12:00:00','2021-06-07 12:00:00');
nonweekendhours
-----------------
24:00:00
(1 row)
SELECT nonweekendhours('2021-06-04 12:00:00','2021-06-11 12:00:00');
nonweekendhours
-----------------
120:00:00
(1 row)
8<----------------------------------------
HTH,
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development