Обсуждение: Need to omit time during weekends from age calculations

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 :-)

Thanks in Advance.

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

Joe Conway <mail@joeconway.com> writes:
> On 6/7/21 3:12 PM, David Gauthier wrote:
>> 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.

I'm a bit suspicious of this problem statement.  I doubt there are many
practical applications where you wouldn't also wish to exclude holidays,
for somebody's definition of holidays.  Of course, that makes it a lot
messier since you need a source of data for that.

            regards, tom lane